Oracle Functions#

SEP provides Oracle compatibility functions. Functions that are provided:

NVL(expr1, expr2)#

NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

INSTR(string, substring[, position][, occurrence])#

Searches a string for a substring and returns the position (in code-points) in the string that is the first character of a specified occurrence of the substring.

TO_DATE(char, fmt)#

Converts VARCHAR to a value of TIMESTAMP using specified formatting (see Oracle Datetime Formatting).

TO_CHAR(datetime, fmt)#

Converts a value of DATE, TIMESTAMP or TIMESTAMP WITH TIME ZONE to a value of VARCHAR using specified formatting (see Oracle Datetime Formatting).

SYSDATE()#

Returns the current TIMESTAMP for the operating system on which the database resides.

Oracle compatibility functions can be enabled via boolean oracle-functions-enabled config property. By default Oracle compatibility functions are disabled. Teradata date functions will be shaded when Oracle functions are enabled.

The DECODE function, available in earlier releases, has been removed. Users can change their SQL statements to achieve the same behavior with the use of a CASE statement.

Features and Limitations#

  • only INSTR is supported from INSTR functions family

  • TO_DATE and TO_CHAR support subset of Oracle datetime formatting (see Oracle Datetime Formatting)

Oracle Datetime Formatting#

Presto supports following Oracle datetime format specifiers:

Element

Notes

D

DD

DDD

DY

Day name will formatted with first uppercase letter (e.g. Mon).

DAY

Name of the day won’t be padded with blanks to display width of the widest name of day.

Day name will formatted with first uppercase letter (e.g. Monday).

HH24

HH12

AM, PM

Only am, AM, pm, PM is supported.

HH

MM

MON

Month name will formatted with first uppercase letter (e.g. Jan).

MONTH

Name of the month won’t be padded with blanks to display width of the widest name of month.

Month name will formatted with first uppercase letter (e.g. January).

MI

SS

SSSSS

FF, FF3

WW

YYYY

YY

TZH:TZM

TZD

Only works with TO_CHAR.

Will show actual offset if there is no named timezone that matches the offset.

TZR