SnowConvert AI - Oracle - Built-in functions¶

CAST

TO_DATE

TO_NUMBER

TO_TIMESTAMP

Not Supported

EXTRACT (datetime)

Not supported

POSITION

REGEXP_INSTR

When the value expression comes with the RESPECT | IGNORE NULLS statement, the statement is moved outside the parenthesis in order to match the Snowflake grammar.

A warning is being added when a default value is present to indicate the Snowflake counterpart does not implicitly cast the default value to the expression type.

COLLATE

Not Supported

In the replace_string parameter (the third one) is being added an extra ‘’ symbol to escape the other one. In the match_param parameter (last one) the equivalence works like this:
‘c’ -> ‘c’ specifies case-sensitive
‘i’ -> ‘i’ specifies case-insensitive
‘n’ -> ‘s’ allows the period(.), which is the match-any-character character, to match the newline character
‘m’ -> ‘m’ treats the source string as multiple lines
‘x’ -> ‘e’ ignores whitespace characters

In the replace_string parameter (the second one) is being added an extra ‘’ symbol to escape the other one. In the match_param parameter the equivalence works like this:
‘c’ -> ‘c’ specifies case-sensitive
‘i’ -> ‘i’ specifies case-insensitive
‘n’ -> ‘s’ allows the period(.), which is the match-any-character character, to match the newline character
‘m’ -> ‘m’ treats the source string as multiple lines
‘x’ -> ‘e’ ignores whitespace characters

SHA1

SHA2

MD5

CURRENT_USER CURRENT_SCHEMA CURRENT_DATABASE IS_ROLE_IN_SESSION CURRENT_CLIENT CURRENT_SESSION
Not supported

Depending on the parameters of the function SYS_CONTEXT, it is converted to one of the specified functions.
‘CURRENT_SCHEMA’ converted to CURRENT_SCHEMA()

‘CURRENT_USER’ converted to CURRENT_USER()

‘DB_NAME’ converted to CURRENT_DATABASE()

‘ISDBA’ converted to IS_ROLE_IN_SESSION(‘DBA’)

‘SERVICE_NAME’ converted to CURRENT_CLIENT()

‘SESSIONID’ converted to CURRENT_SESSION()

‘GUEST’ converted to IS_ROLE_IN_SESSION(‘GUEST’)

‘SESSION_USER’ converted to CURRENT_USER()

‘AUTHENTICATED_IDENTITY’ converted to CURENT_USER()

When a parameter is not supported it is converted to stub ‘SYS_CONTEXT_STUB’

TO_CHAR(datetime) Conditional Expression(CASE)
Not Supported

When comes a format not supported, the function is commented out and an error is added. Not supported formats: FXFMDD-MON-YYYY
J
DDD
MONTH
RM
DD-MON-RR
DD-MON-RRRR
SSSSS
YYYY
YYY
Y

TO_NUMBER

Not Supported

The ‘DEFAULT integer ON CONVERSION ERROR’ statement is removed and outputs an error,

Converted to a stub TO_NUMBER_STUB and an error is added when the ‘format’ parameter is not supported and also when the function has the ‘nlsparam’ parameter.

When comes a format not supported, the function is commented out and an error is added. Not supported formats: FXFMDD-MON-YYYY
J
DDD
MONTH
RM
DD-MON-RR
DD-MON-RRRR
SSSSS
YYYY
YYY
Y

When comes a format not supported, the function is commented out and an error is added. Not supported formats: FXFMDD-MON-YYYY
J
DDD
MONTH
RM
DD-MON-RR
DD-MON-RRRR
SSSSS
YYYY
YYY
Y

TRIM

LTRIM

RTRIM

Depending on the first parameter it will be converted to:
LEADING keyword -> LTRIM TRAILING keyword -> RTRIM BOTH keyword -> TRIM
None of these keywords -> keep as TRIM function. Also, the order of the ‘trimsource’ parameter and the ‘trimcharacter’ parameter is inverted, and the FROM keyword is removed from the function.

AD or BC

A.D. or B.C.

The function will be converted to a conditional expression (CASE)
where the format is added as a result of the ‘when’ condition.
For Example:
from: To_Char(DATE '1998-12-25', 'AD')
to: CASE WHEN YEAR(DATE '1998-12-25') < 0 THEN'BC'

The function will be converted to a conditional expression where
the original function body is added as a when condition but it will be between

a MOD function, after that the original function is added as a then result but
contained by a SUBSTR function.
For example:

from: To_Char(DATE '1998-12-25','CC')
to: CASE WHEN MOD(YEAR(DATE '1998-12-25'), 100) = 0
THEN SUBSTR(TO_CHAR(DATE '1998-12-25', 'YYYY'), 1, 2)

The function will be converted to the snowflake function equivalent but
the function body will be between the DAYOFWEEK datetime part.

For Example:

from: To_Char(DATE '1998-12-25','D')

to: TO_CHAR(DAYOFWEEK(DATE '1998-12-25') + 1)

The function will be converted to a user-defined function inside of an UPPER
function.
For Example:
from: To_Char(DATE '1998-12-25','DAY')

to: UPPER(SNOWCONVERT.PUBLIC.FULL_DAY_NAME_UDF(DATE '1998-12-25'))

The function will be converted to the snowflake function equivalent but
the function body will be between the DAYOFYEAR datetime part.

For Example:
from: To_Char(DATE '1998-12-25','DDD')

to: TO_CHAR(DAYOFYEAR(DATE '1998-12-25'))

The function will be converted to the snowflake function equivalent keeping the

function body but changing the format to: ‘DD-MON-YY’.

For Example:

from: To_Char(TIMESTAMP '1998-12-25 09:26:50.12','DD-MON-RR')

to: To_Char(TIMESTAMP '1998-12-25 09:26:50.12','DD-MON-YY')

The function will be converted to a user-defined function plus the ‘OR’ operator
plus snowflake equivalent keeping the function body but changing the format

to: ‘, MMM DD, YYYY

For example:

from: To_Char(DATE '1998-12-25','DL')

to: SNOWCONVERT.PUBLIC.FULL_DAY_NAME_UDF(DATE '1998-12-25')

The function will be converted to a combination of the snowflake function

equivalent inside of the LTRIM function and the snowflake function equivalent.

All the parts combined with the ‘OR’ operator.

For Example:

from: To_Char(DATE '1998-12-25','DS')

to: LTRIM(TO_CHAR(DATE '1998-12-25', 'MM'), '0')

The function will be converted to the snowflake function equivalent

inside of the UPPER function.

For example:
from: To_Char(DATE '1998-12-25','DY')
to: UPPER(TO_CHAR(DATE '1998-12-25', 'DY'))

The function will be converted to the snowflake function equivalent

inside of the SUBSTR function.

For Example:

from: To_Char(DATE '1998-12-25','I')

to: SUBSTR(TO_CHAR(DATE '1998-12-25', 'YYYY'), 4, 1)

The function will be converted to the snowflake function equivalent but
the function body will be between the WEEKISO datetime part.

For Example:

from:To_Char(DATE '1998-12-25','IW')

to: TO_CHAR(WEEKISO(DATE '1998-12-25'))

The function will be converted to the snowflake function equivalent keeping the

function body but changing the format to: ‘YY’.

For example:

from:To_Char(DATE '1998-12-25', 'IY')

to: TO_CHAR(DATE '1998-12-25', 'YY')

The function will be converted to the snowflake function equivalent

inside of the SUBSTR function and change the format to: ‘YYYY’.

For Example:

from: To_Char(DATE '1998-12-25','IYY')

to: SUBSTR(TO_CHAR(DATE '1998-12-25', 'YYYY'), 2, 3)

The function will be converted to the snowflake function equivalent keeping the

function body but changing the format to: ‘YYYY’.

For example:

from:To_Char(DATE '1998-12-25', 'IYYY')

to: TO_CHAR(DATE '1998-12-25', 'YYYY')

The function will be converted to a conditional expression with ‘B.C.’ as a ‘then’

result and ‘A.D.’ as an else result.

For example:

from: To_Char(DATE '1998-12-25','J')

to: DATE_TO_JULIANDAYS_UDF(DATE ’1998-12-25’)

The function will be converted to the snowflake equivalent. If the function

argument is SYSDATE it will be changed to CURRENT_TIMESTAMP, otherwise,

if it is of type date, the function will return null.

For Example:

from: To_Char(SYSDATE,'MI');

to: To_Char(CURRENT_TIMESTAMP,'MI')

The function will be converted to the snowflake function equivalent
inside of the UPPER function.

For Example:

from: To_Char(DATE '1998-12-25','MON')

to: UPPER(TO_CHAR(DATE '1998-12-25', 'MON'))

The function will be converted to the snowflake function equivalent

inside of the UPPER function and change the format to: ‘MMMM’.

For Example:

from: To_Char(DATE '1998-12-25','MONTH')

to: UPPER(TO_CHAR(DATE '1998-12-25', 'MMMM'))

The function will be converted to the snowflake function equivalent
inside of the QUARTER function.

For Example:

from: To_Char(DATE '1998-12-25','Q')

to: TO_CHAR(QUARTER(DATE '1998-12-25'))

The function will be converted to a user-defined function.

For Example:

from: To_Char(DATE '1998-12-25','RM')

to: SNOWCONVERT.PUBLIC.ROMAN_MONTH_UDF(DATE '1998-12-25')

The function will be converted to the snowflake function equivalent keeping the

function body but changing the format to: ‘YY’.

For Example:

from: To_Char(DATE '1998-12-25','RR')

to: TO_CHAR(DATE '1998-12-25', 'YY')

The function will be converted to the snowflake function equivalent keeping the

function body but changing the format to: ‘YY-MON-DD’.

For Example:

from: To_Char(TIMESTAMP '1998-12-25 09:26:50.12','RR-MON-DD')

to: To_Char(TIMESTAMP '1998-12-25 09:26:50.12','YY-MON-DD')

The function will be converted to the snowflake function equivalent keeping the

function body but changing the format to: ‘YYYY’.

For Example:

from: To_Char(DATE '1998-12-25','RRRR')

to: TO_CHAR(DATE '1998-12-25', 'YYYY')

The function will be converted to a combination of a conditional expression
and the snowflake function equivalent.

All the parts combined with the ‘OR’ operator.
For Example:
from: To_Char(TIMESTAMP '1998-12-25 09:26:50.12','SS')

to: CASE WHEN SECOND(TIMESTAMP '1998-12-25 09:26:50.12') = 0
THEN '00' WHEN SECOND(TIMESTAMP '1998-12-25 09:26:50.12') < 10
THEN '0'

The function will be converted to the snowflake function equivalent but the

function body will be a concatenation of SECOND, MINUTE, and HOUR datetime parts.

For Example:

from: To*Char(TIMESTAMP '1998-12-25 09:26:50.12','SSSS')

to: TO_CHAR(SECOND(TIMESTAMP '1998-12-25 09:26:50.12') +
MINUTE(TIMESTAMP '1998-12-25 09:26:50.12') * 60 +
HOUR(TIMESTAMP '1998-12-25 09:26:50.12') _ 3600)

The function will be converted to the snowflake function equivalent keeping the

function body but changing the format to: ‘HH:MI:SS PM’.

For Example:

from: To_Char(TIMESTAMP '1998-12-25 09:26:50.12','TS')

to: TO_CHAR(TIMESTAMP '1998-12-25 09:26:50.12', 'HH:MI:SS PM')

The function will be converted to the TRUNC function with the DAYOFMONTH
datetime part.

For Example:

from: To_Char(DATE '1998-12-25','W')

to: TRUNC(DAYOFMONTH(DATE '1998-12-25') / 7 + 1)

The function will be converted to the TRUNC function with the DAYOFYEAR
datetime part.

For Example:

from: To_Char(DATE '1998-12-25','WW')

to: TRUNC(DAYOFYEAR(DATE '1998-12-25') / 7 + 1)

Y

YYY

The function will be converted to the snowflake function equivalent

inside of the SUBSTR function and change the format to: ‘YYYY’.

For Example:

from: To_Char(DATE '1998-12-25','Y')

to: SUBSTR(TO_CHAR(DATE '1998-12-25', 'YYYY'), 4, 1)

The function will be converted to a combination of the snowflake function
equivalent inside of the SUBSTR function and a comma symbol.
All the parts combined with the ‘OR’ operator.

For Example:

from: To_Char(DATE '1998-12-25','Y,YYY')

to: SUBSTR(TO_CHAR(YEAR(DATE '1998-12-25')), 1, 1)

YEAR

SYEAR

The function will be converted to a user-defined function inside of an UPPER
function.

For Example:

from: To_Char(DATE '1998-12-25','YEAR')

to: UPPER(SNOWCONVERT.PUBLIC.YEAR_NAME_UDF(DATE '1998-12-25'))