14.26. Oracle functions

Starburst Presto provides Oracle compatibility functions. Functions that are provided:

DECODE(expr, search1, result1, ..., searchN, resultN[, default])

DECODE compares expr to each search value one by one. If expr is equal to a searchK, then the corresponding resultK is returned. If no match is found, then default is returned. If default is omitted, then null is returned.

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.

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