SnowConvert: Built-in functions¶
Functions¶
Oracle |
Snowflake |
Notes |
---|---|---|
ABS |
ABS |
|
ACOS |
ACOS |
|
ADD_MONTHS |
ADD_MONTHS |
|
ANY_VALUE |
ANY_VALUE |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
APPROX_COUNT |
*to be defined |
|
APPROX_COUNT_DISTINCT |
APPROX_COUNT_DISTINCT |
|
APPROX_COUNT_DISTINCT_AGG |
*to be defined |
|
APPROX_COUNT_DISTINCT_DETAIL |
*to be defined |
|
APPROX_MEDIAN |
*to be defined |
|
APPROX_PERCENTILE |
APPROX_PERCENTILE |
|
APPROX_PERCENTILE_AGG |
*to be defined |
|
APPROX_PERCENTILE_DETAIL |
*to be defined |
|
APPROX_RANK |
*to be defined |
|
APPROX_SUM |
*to be defined |
|
ASCII |
ASCII |
|
ASCIISTR |
*to be defined |
|
ASIN |
ASIN |
|
ATAN |
ATAN |
|
ATAN2 |
ATAN2 |
|
AVG |
AVG |
|
BFILENAME |
*to be defined |
|
BIN_TO_NUM |
*to be defined |
|
BITAND |
BITAND |
|
BIT_AND_AGG |
BITAND_AGG |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
BITMAP_BIT_POSITION |
BITMAP_BIT_POSITION |
|
BITMAP_BUCKET_NUMBER |
BITMAP_BUCKET_NUMBER |
|
BITMAP_CONSTRUCT___AGG |
BITMAP_CONSTRUCT___AGG |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
BITMAP_COUNT |
BITMAP_BIT_COUNT |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
BITMAP_OR_AGG |
BITMAP_OR___AGG |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
BIT_OR_AGG |
BIT_OR_AGG |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
BIT_XOR_AGG |
BIT_XOR_AGG |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
CARDINALITY |
*to be defined |
|
CAST |
CAST TO_DATE TO_NUMBER TO_TIMESTAMP Not Supported |
The function is converted to stub ‘CAST_STUB’ and outputs an error, when comes with one of the following not supported statement: ‘DEFAULT ON CONVERSION ERROR’ or ‘MULTISET’. Also, it is converted to a stub and outputs an error if the data type is not supported. The function is converted to the ‘TO_NUMBER’ function when the expression to cast is of type number and outputs an error indicating that the explicit cast is not possible to be done. The function is converted to the ‘TO_DATE’ function when the expression to cast is of type date and outputs an error indicating that the explicit cast is not possible to be done. The function is converted to the ‘TO_TIMESTAMP’ function when the expression to cast is of type timestamp and outputs an error indicating that the explicit cast is not possible to be done. |
CEIL |
CEIL |
|
CHARTOROWID |
*to be defined |
|
CHECKSUM |
*to be defined |
|
CHR |
CHR |
USING NCHAR_CS statement is not supported by the Snowflake function equivalent. The clause is removed. |
CLUSTER_DETAILS |
*to be defined |
|
CLUSTER_DISTANCE |
*to be defined |
|
CLUSTER_ID |
*to be defined |
|
CLUSTER_PROBABILITY |
*to be defined |
|
CLUSTER_SET |
*to be defined |
|
COALESCE |
COALESCE |
|
COLLATION |
COLLATION |
|
COLLECT |
*to be defined |
|
COMPOSE |
*to be defined |
|
CON_DBID_TO_ID |
*to be defined |
|
CON_GUID_TO_ID |
*to be defined |
|
CON_NAME_TO_ID |
*to be defined |
|
CON_UID_TO_ID |
*to be defined |
|
CONCAT |
CONCAT |
Every expression parameter will be inside of an NVL(expr, ‘ ‘) function to avoid an error in case one of the expressions is null. |
CONVERT |
*to be defined |
|
CORR |
CORR |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
CORR_S |
*to be defined |
|
CORR_K |
*to be defined |
|
COS |
COS |
|
COSH |
COSH |
|
COUNT |
COUNT |
|
COVAR_POP |
COVAR_POP |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
COVAR_SAMP |
COVAR_SAMP |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
CUBE_TABLE |
Not Supported |
Converted to a stub ‘CUBE_TABLE_STUB’ and an error is added. |
CUME_DIST |
CUME_DIST |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
CURRENT_DATE |
CURRENT_DATE |
|
CURRENT_TIMESTAMP |
CURRENT_TIMESTAMP |
|
CV |
*to be defined |
|
DATAOBJ_TO_MAT_PARTITION |
*to be defined |
|
DATAOBJ_TO_PARTITION |
*to be defined |
|
DBTIMEZONE |
*to be defined |
|
DECODE |
DECODE |
|
DECOMPOSE |
*to be defined |
|
DENSE_RANK |
DENSE_RANK |
There are two kinds of syntax, aggregate syntax, and analytic syntax. The aggregate syntax is not supported and an error is added. The analytic syntax is supported but the ‘SIBLINGS’ keyword is removed from the ‘order by’ clause and a warning is added. |
DEPTH |
*to be defined |
|
DEREF |
*to be defined |
|
DUMP |
*to be defined |
|
EMPTY_BLOB |
*to be defined |
|
EMPTY_CLOB |
*to be defined |
|
EXISTSNODE |
*to be defined |
|
EXP |
EXP |
|
EXTRACT (datetime) |
EXTRACT (datetime) Not supported |
Kept as an EXTRACT function but outputs a warning when the function has ‘MINUTE’ or ‘TIMEZONE_MINUTE’ as the first keyword parameter. Converted to a stub ‘EXTRACT_STUB’ and outputs an error when the first keyword parameter is ‘TIMEZOME_REGION’ or ‘TIMEZONE_ABBR’ |
EXTRACT (XML) |
Not Supported |
Function related to XML is not supported. It is converted to a stub ‘EXTRACT_STUB’ and an error is added. Please check the following link about how to handle the loading for XML: |
EXTRACTVALUE |
Not Supported |
Converted to a stub ‘EXTRACTVALUE_STUB’ and an error is added. |
FEATURE_COMPARE |
*to be defined |
|
FEATURE_DETAILS |
*to be defined |
|
FEATURE_ID |
*to be defined |
|
FEATURE_SET |
*to be defined |
|
FEATURE_VALUE |
*to be defined |
|
FIRST |
Not Supported |
The statement used to indicate that only the first or last values of the aggregate function will be returned is not supported. Outputs an error. |
FIRST_VALUE |
FIRST_VALUE |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
FLOOR |
FLOOR |
|
FROM_TZ |
*to be defined |
|
GREATEST |
GREATEST |
|
GROUP_ID |
*to be defined |
|
GROUPING |
GROUPING |
|
GROUPING_ID |
GROUPING_ID |
|
HEXTORAW |
*to be defined |
|
INITCAP |
INITCAP |
|
INSTR |
POSITION |
The order of the ‘string’ parameter and the ‘substring’ parameter is inverted. Also, the ‘occurrence’ parameter is removed because it is not supported and a warning is added. |
ITERATION_NUMBER |
*to be defined |
|
JSON_ARRAY |
*to be defined |
|
JSON_ARRAYAGG |
*to be defined |
|
JSON |
*to be defined |
|
JSON_MERGE_PATCH |
*to be defined |
|
JSON_OBJECT |
*to be defined |
|
JSON_OBJECTAGG |
*to be defined |
|
JSON_QUERY |
*to be defined |
|
JSON_SCALAR |
*to be defined |
|
JSON_SERIALIZE |
*to be defined |
|
JSON_TABLE |
Not Supported |
Outputs an error: JSON_TABLE IS NOT SUPPORTED. |
JSON_TRANSFORM |
*to be defined |
|
JSON_VALUE |
||
KURTOSIS_POP |
*to be defined |
|
KURTOSIS_SAMP |
*to be defined |
|
LAG |
LAG |
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. |
LAST |
Not Supported |
The statement used to indicate that only the first or last values of the aggregate function will be returned is not supported. Outputs an error. |
LAST_DAY |
LAST_DAY |
|
LAST_VALUE |
LAST_VALUE |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
LEAD |
LEAD |
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. |
LEAST |
LEAST |
|
LENGTH |
LENGTH |
|
LISTAGG |
LISTAGG |
The overflow clause is removed from the function. |
LN |
LN |
|
LNNVL |
*to be defined |
|
LOCALTIMESTAMP |
LOCALTIMESTAMP |
|
LOG |
LOG |
|
LOWER |
LOWER |
|
LPAD |
LPAD |
|
LTRIM |
LTRIM |
|
MAKE_REF |
*to be defined |
|
MAX |
MAX |
|
MEDIAN |
MEDIAN |
|
MIN |
MIN |
|
MOD |
MOD |
|
MONTHS_BETWEEN |
MONTHS_BETWEEN_UDF |
Converted to a user-defined function. |
NANVL |
*to be defined |
|
NCHR |
*to be defined |
|
NEW_TIME |
*to be defined |
|
NEXT_DAY |
NEXT_DAY |
|
NLS_CHARSET_DESCL_LEN |
*to be defined |
|
NLS_CHARSET_ID |
*to be defined |
|
NLS_CHARSET_NAME |
*to be defined |
|
NLS_COLLATION_ID |
*to be defined |
|
NLS_COLLATION_NAME |
*to be defined |
|
NLS_INITCAP |
*to be defined |
|
NLS_LOWER |
*to be defined |
|
NLS_UPPER |
*to be defined |
|
NLSSORT |
COLLATE Not Supported |
When the function is outside of a ‘where’ or ‘order by’ clause, it is not supported and it is converted to stub ‘NLSSORT_STUB’ and an error is added. Otherwise, if the function is inside a ‘where’ or ‘order by’ clause, it is converted to the COLLATE function. |
NTH_VALUE |
NTH_VALUE |
|
NTILE |
NTILE |
|
NULLIF |
NULLIF |
|
NUMTODSINTERVAL |
Not Supported |
While the function itself is not supported, some usages can be migrated manually. For example DATEADD can be used to manually migrate a sum between a Date/Timestamp and this function. |
NUMTOYMINTERVAL |
Not Supported |
While the function itself is not supported, some usages can be migrated manually. For example DATEADD can be used to manually migrate a sum between a Date/Timestamp and this function. |
NVL |
NVL |
|
NVL2 |
NVL2 |
|
ORA_DM_PARTITION_NAME |
*to be defined |
|
ORA_DST_AFFECTED |
*to be defined |
|
ORA_DST_CONVERTED |
*to be defined |
|
ORA_DST_ERROR |
*to be defined |
|
ORA_HASH |
Not Supported |
Converted to a stub ‘ORA_HASH_STUB’ and an error is added. |
PATH |
*to be defined |
|
PERCENT_RANK |
PERCENT_RANK |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
PERCENTILE_CONT |
PERCENTILE_CONT |
|
PERCENTILE_DISC |
PERCENTILE_DISC |
|
POWER |
POWER |
|
POWERMULTISET |
*to be defined |
|
POWERMULTISET_BY_CARDINALITY |
*to be defined |
|
PREDICTION |
*to be defined |
|
PREDICTION_BOUNDS |
*to be defined |
|
PREDICTION_COST |
*to be defined |
|
PREDICTION_DETAILS |
*to be defined |
|
PREDICTION_PROBABILITY |
*to be defined |
|
PREDICTION_SET |
*to be defined |
|
PRESENTNNV |
*to be defined |
|
PRESENTV |
*to be defined |
|
PREVIOUS |
*to be defined |
|
RANK |
RANK |
There are two kinds of syntax, aggregate syntax, and analytic syntax. The aggregate syntax is not supported and an error is added. The analytic syntax is supported but the ‘SIBLINGS’ keyword is removed from the ‘order by’ clause and a warning is added. |
RATIO_TO_REPORT |
RATIO_TO_REPORT |
|
RAWTOHEX |
*to be defined |
|
RAWTONHEX |
*to be defined |
|
REF |
*to be defined |
|
REFTOHEX |
*to be defined |
|
REGEXP_COUNT |
REGEXP_COUNT |
|
REGEXP_INSTR |
REGEXP_INSTR |
|
REGEXP_REPLACE |
REGEXP_REPLACE |
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: |
REGEXP_SUBSTR |
REGEXP_SUBSTR |
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: |
REGR |
REGR |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
REMAINDER |
*to be defined |
|
REPLACE |
REPLACE |
|
REVERSE |
REVERSE |
|
ROUND |
ROUND |
|
ROUND_TIES_TO_EVEN |
*to be defined |
|
ROW_NUMBER |
ROW_NUMBER |
|
RPAD |
RPAD |
|
ROWIDTOCHAR |
*to be defined |
|
ROWIDTONCHAR |
*to be defined |
|
RTRIM |
RTRIM |
|
SCN_TO_TIMESTAMP |
*to be defined |
|
SESSIONTIMEZONE |
*to be defined |
|
SET |
*to be defined |
|
SIGN |
SIGN |
|
SINH |
SINH |
|
SKEWNESS_POP |
*to be defined |
|
SKEWNESS_SAMP |
*to be defined |
|
SOUNDEX |
SOUNDEX |
|
SQRT |
SQRT |
|
STANDARD_HASH |
*to be defined |
|
STATS_BINOMIAL_TEST |
*to be defined |
|
STATS_CROSSTAB |
*to be defined |
|
STATS_F_TEST |
*to be defined |
|
STATS_KS_TEST |
*to be defined |
|
STATS_MODE |
*to be defined |
|
STATS_MW_TEST |
*to be defined |
|
STATS_ONE_WAY_ANOVA |
*to be defined |
|
STATS_T_TEST |
*to be defined |
|
STATS_WSR_TEST |
*to be defined |
|
STDDEV |
STDDEV |
|
STDDEV_POP |
STDDEV_POP |
|
STDDEV_SAMP |
STDDEV_SAMP |
|
SUBSTR |
SUBSTR |
All the types of SUBSTR (SUBSTRB, SUBSTRC, SUBSTR2, SUBSTR4) are being converted to SUBSTR |
SUM |
SUM |
|
SYS_CONNECT_BY_PATH |
*to be defined |
|
SYS_CONTEXT |
CURRENT_USER CURRENT_SCHEMA CURRENT_DATABASE IS_ROLE_IN_SESSION CURRENT_CLIENT CURRENT_SESSION |
Depending on the parameters of the function SYS_CONTEXT, it is converted to one of the specified functions.
|
SYS_DBURIGEN |
*to be defined |
|
SYS_EXTRACT_UTC |
*to be defined |
|
SYS_GUID |
*to be defined |
|
SYS_OP_ZONE_ID |
*to be defined |
|
SYS_TYPEID |
*to be defined |
|
SYS_XMLAGG |
*to be defined |
|
SYS_XMLGEN |
*to be defined |
|
TAN |
TAN |
|
TANH |
TANH |
|
TIMESTAMP_TO_SCN |
*to be defined |
|
TO_APPROX_COUNT_DISTINCT |
*to be defined |
|
TO_APPROX_PERCENTILE |
*to be defined |
|
TO_BINARY_DOUBLE |
*to be defined |
|
TO_BINARY_FLOAT |
*to be defined |
|
TO_BLOB (bfile) |
*to be defined |
|
TO_BLOB (raw) |
*to be defined |
|
TO_CHAR (character) |
TO_CHAR |
|
TO_CHAR (datetime) |
TO_CHAR(datetime) Conditional Expression(CASE) |
Depending on the format parameter, the function is converted to conditional expression (CASE WHEN) or a user-defined function or kept as TO_CHAR(datetime). Sometimes the function will be between another function to get an equivalent result. When the function is not supported it is converted to stub ‘TO_CHAR_STUB’. Go to To_Char(datetime) to get more information about this function. |
TO_CHAR (number) |
TO_CHAR (number) |
If the numeric parameter is of type double or float the function is commented out and an error is added. When comes a format not supported, the format parameter is removed from the function and an error is added. Not supported formats: C L PR RN TM U V. If the function has the nlsparam parameter, it is removed from the function and an error is added. |
TO_CLOB ( bfile | blob ) |
TO_VARCHAR |
Outputs a warning to indicate the bfile/blob parameters are considered binary. Also outputs an error when the function has more than one parameter. |
TO_CLOB (character) |
TO_VARCHAR |
Outputs a warning to indicate the bfile/blob parameters are considered binary. Also outputs an error when the function has more than one parameter. |
TO_DATE |
TO_DATE |
When comes a format not supported, the function is commented out and an error is added. Not supported formats: FXFMDD-MON-YYYY |
TO_DSINTERVAL |
*to be defined |
|
TO_LOB |
*to be defined |
|
TO_MULTI_BYTE |
*to be defined |
|
TO_NCHAR |
*to be defined |
|
TO_NCHAR (datetime) |
*to be defined |
|
TO_NCLOB |
*to be defined |
|
TO_NUMBER |
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. |
TO_SINGLE_BYTE |
*to be defined |
|
TO_TIMESTAMP |
TO_DATE |
When comes a format not supported, the function is commented out and an error is added. Not supported formats: FXFMDD-MON-YYYY |
TO_TIMESTAMP_TZ |
TO_DATE |
When comes a format not supported, the function is commented out and an error is added. Not supported formats: FXFMDD-MON-YYYY |
TO_UTC_TIMESTAMP_TZ |
*to be defined |
|
TO_YMINTERVAL |
*to be defined |
|
TRANSLATE |
TRANSLATE |
|
TRANSLATE_USING |
TRANSLATE_USING |
|
TREAT |
*to be defined |
|
TRIM |
TRIM LTRIM RTRIM |
Depending on the first parameter it will be converted to: |
TRUNC (date) |
TRUNC(date) |
‘DAY’ expression is added as a second parameter of the function. |
TRUNC (number) |
TRUNC(number) |
|
TZ_OFFSET |
*to be defined |
|
UID |
*to be defined |
|
UNISTR |
TO_VARCHAR(expr) |
In the expr parameter is being added the ‘u’ letter after every ‘' symbol. |
UPPER |
UPPER |
|
USER |
*to be defined |
|
USERNV |
*to be defined |
|
VALIDATE_CONVERSION |
*to be defined |
|
VALUE |
Not Supported |
Converted to a stub ‘VALUE_STUB’ and an error is added. |
VAR_POP |
VAR_POP |
|
VAR_SAMP |
VAR_SAMP |
|
VARIANCE |
VARIANCE |
A warning is being added to indicate the Snowflake counterpart may not be functionally equivalent. |
VSIZE |
*to be defined |
|
WIDTH_BUCKET |
WIDTH_BUCKET |
|
XMLAGG |
*to be defined |
|
XMLCAST |
*to be defined |
|
XMLCDATA |
*to be defined |
|
XMLCOLATVAL |
*to be defined |
|
XMLCOMMENT |
*to be defined |
|
XMLCONCAT |
*to be defined |
|
XMLDIFF |
*to be defined |
|
XMLELEMENT |
*to be defined |
|
XMLEXISTS |
*to be defined |
|
XMLFOREST |
*to be defined |
|
XMLISVALID |
*to be defined |
|
XMLPARSE |
*to be defined |
|
XMLPATCH |
*to be defined |
|
XMLPI |
*to be defined |
|
XMLQUERY |
Not Supported |
|
XMLSEQUENCE |
Not Supported |
Converted to a stub ‘XMLSEQUENCE_STUB’ and an error is added. |
XMLSERIALIZE |
*to be defined |
|
XMLTABLE |
Not Supported |
Outputs an error: XMLTABLE IS NOT SUPPORTED. |
XMLTRANSFORM |
*to be defined |
Functions Details.¶
To_Char(datetime)¶
According to the format parameter, the function will be converted to:
Format |
Conversion |
---|---|
AD or BC A.D. or B.C. |
The function will be converted to a conditional expression (CASE) |
CC or SCC |
The function will be converted to a conditional expression where a MOD function, after that the original function is added as a then result but
|
D |
The function will be converted to the snowflake function equivalent but For Example:
|
DAY |
The function will be converted to a user-defined function inside of an UPPER
|
DDD |
The function will be converted to the snowflake function equivalent but For Example:
|
DD-MON-RR |
The function will be converted to the snowflake function equivalent keeping the function body but changing the format to: ’DD-MON-YY’. For Example:
|
DL |
The function will be converted to a user-defined function plus the ’OR’ operator to: ’, MMM DD, YYYY For example:
|
DS |
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:
|
DY |
The function will be converted to the snowflake function equivalent inside of the UPPER function. For example: |
I |
The function will be converted to into the snowflake function equivalent inside of the SUBSTR function. For Example:
|
IW |
The function will be converted to the snowflake function equivalent but For Example:
|
IY |
The function will be converted to the snowflake function equivalent keeping the function body but changing the format to: ’YY’. For example:
|
IYY |
The function will be converted to the snowflake function equivalent inside of the SUBSTR function and change the format to: ’YYYY’. For Example:
|
IYYY |
The function will be converted to the snowflake function equivalent keeping the function body but changing the format to: ’YYYY’. For example:
|
J |
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:
|
MI |
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:
|
MON |
The function will be converted to the snowflake function equivalent For Example:
|
MONTH |
The function will be converted to the snowflake function equivalent inside of the UPPER function and change the format to: ’MMMM’. For Example:
|
Q |
The function will be converted to the snowflake function equivalent For Example:
|
RM |
The function will be converted to a user-defined function. For Example:
|
RR |
The function will be converted to the snowflake function equivalent keeping the function body but changing the format to: ’YY’. For Example:
|
RR-MON-DD |
The function will be converted to the snowflake function equivalent keeping the function body but changing the format to: ’YY-MON-DD’. For Example:
|
RRRR |
The function will be converted to the snowflake function equivalent keeping the function body but changing the format to: ’YYYY’. For Example:
|
SS |
The function will be converted to a combination of a conditional expression All the parts combined with the ’OR’ operator.
|
SSSS |
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:
|
TS |
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:
|
W |
The function will be converted to the TRUNC function with the DAYOFMONTH For Example:
|
WW |
The function will be converted to the TRUNC function with the DAYOFYEAR For Example:
|
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:
|
Y,YYY |
The function will be converted to a combination of the snowflake function For Example:
|
YEAR SYEAR |
The function will be converted to a user-defined function inside of an UPPER For Example:
|
NLSSORT¶
Description¶
NLSSORT returns a collation key for the character value char and an explicitly or implicitly specified collation. A collation key is a string of bytes used to sort char according to the specified collation. The property of the collation keys is that mutual ordering of two such keys generated for the given collation when compared according to their binary order is the same as mutual ordering of the source character values when compared according to the given collation.. (NLSSORT in Oracle).
Sample Source Pattern¶
Syntax¶
NLSSORT(char [, 'nlsparam' ])
COLLATE(<string_expression>, '<collation_specification>')
Examples¶
Code:
CREATE TABLE test (name VARCHAR2(15));
INSERT INTO test VALUES ('Gaardiner');
INSERT INTO test VALUES ('Gaberd');
INSERT INTO test VALUES ('Gaasten');
SELECT *
FROM test
ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');
Result:
:force:
NAME
-------
Gaberd
Gaardiner
Gaasten
Code:
CREATE OR REPLACE TABLE test (name VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO test
VALUES ('Gaardiner');
INSERT INTO test
VALUES ('Gaberd');
INSERT INTO test
VALUES ('Gaasten');
SELECT *
FROM
test
ORDER BY
COLLATE(name, '');
Result:
NAME
-------
Gaberd
Gaardiner
Gaasten
TO_NUMBER¶
Description¶
Converts an input expression to a fixed-point number. For NULL input, the output is NULL.
Arguments¶
Required:
<expr>
An expression of a numeric, character, or variant type.
Optional:
<format>
The SQL format model used to parse the input expr
and return. For more information, see SQL Format Models.
<precision>
The maximal number of decimal digits in the resulting number; from 1 to 38. In Snowflake, precision is not used for determination of the number of bytes needed to store the number and does not have any effect on efficiency, so the default is the maximum (38).
<scale>
The number of fractional decimal digits (from 0 to precision
- 1). 0 indicates no fractional digits (i.e. an integer number). The default scale is 0.
Returns¶
The function returns NUMBER(
precision
,`` ``
scale
)
.
If the
precision
is not specified, then it defaults to 38.If the
scale
is not specified, then it defaults to 0.
To more information check the TO_NUMBER in snowflake documentation.
SELECT CAST('123,456E+40' AS NUMBER, '999,999EEE') FROM DUAL;
SELECT CAST('12sdsd3,456E+40' AS NUMBER, '999,999EEE') FROM DUAL;
SELECT CAST('12345sdsd' AS NUMBER, '99999') FROM DUAL;
SELECT CAST('12.345678912345678912345678912345678912' AS NUMBER, '99.999999999999999999999999999999999999') FROM DUAL;
SELECT CAST(' 12.345678912345678912345678912345678912' AS NUMBER, '99.999999999999999999999999999999999999') FROM DUAL;
SELECT CAST(' -12.345678912345678912345678912345678912' AS NUMBER, '99.999999999999999999999999999999999999') FROM DUAL;
SELECT CAST('12.34567891234567891234567891234567891267' AS NUMBER, '99.999999999999999999999999999999999999') FROM DUAL;
SELECT CAST('123.456E-40' AS NUMBER, '999.9999EEE') FROM DUAL;
select cast('12,345,678,912,345,678,912,345,678,912,345,678,912' as number, '99,999,999,999,999,999,999,999,999,999,999,999,999') from dual;
SELECT CAST(' 123.456E-40' AS NUMBER, '999.9999EEE') FROM DUAL;
select cast(' 12,345,678,912,345,678,912,345,678,912,345.678912' as number, '99,999,999,999,999,999,999,999,999,999,999.999999') from dual;
SELECT CAST('12.34567891234567891234567891234567891267+' AS NUMBER, '99.999999999999999999999999999999999999S') FROM DUAL;
select cast('12,345,678,912,345,678,912,345,678,912,345,678,912+' as number, '99,999,999,999,999,999,999,999,999,999,999,999,999S') from dual;
select cast('12.48+' as number, '99.99S') from dual;
select cast(' 12.48+' as number, '99.99S') from dual;
select cast('12.48+ ' as number, '99.99S') from dual;
SELECT CAST('123.456+E-2' AS NUMBER, '999.9999SEEE') FROM DUAL;
SELECT CAST('123.456+E-2-' AS NUMBER, '999.9999SEEE') FROM DUAL;
SELECT CAST('12356-' AS NUMBER, '99999S') FROM DUAL;
select cast(' 1.0E+123' as number, '9.9EEEE') from dual;
select cast('1.2E+02' as number, 'FM9.9EEEE') from dual;
select cast('123.45' as number, 'FM999.009') from dual;
select cast('123.00' as number, 'FM999.009') from dual;
select cast(' $123.45' as number, 'L999.99') from dual;
select cast('$123.45' as number, 'FML999.99') from dual;
select cast('1234567890+' as number, '9999999999S') from dual;
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0050 - INPUT EXPRESSION IS OUT OF THE RANGE '123,456E+40' ***/!!!
CAST('123,456E+40' AS NUMBER(38, 18) , '999,999EEE') FROM DUAL;
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0053 - INCORRECT INPUT FORMAT '12sdsd3,456E+40' ***/!!! CAST('12sdsd3,456E+40' AS NUMBER(38, 18) , '999,999EEE') FROM DUAL;
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0053 - INCORRECT INPUT FORMAT '12345sdsd' ***/!!! CAST('12345sdsd' AS NUMBER(38, 18) , '99999') FROM DUAL;
SELECT
TO_NUMBER('12.345678912345678912345678912345678912', '99.999999999999999999999999999999999999', 38, 36)
FROM DUAL;
SELECT
TO_NUMBER(' 12.345678912345678912345678912345678912', '99.999999999999999999999999999999999999', 38, 36)
FROM DUAL;
SELECT
TO_NUMBER(' -12.345678912345678912345678912345678912', '99.999999999999999999999999999999999999', 38, 36)
FROM DUAL;
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0050 - INPUT EXPRESSION IS OUT OF THE RANGE '12.34567891234567891234567891234567891267' ***/!!! CAST('12.34567891234567891234567891234567891267' AS NUMBER(38, 18) , '99.999999999999999999999999999999999999') FROM DUAL;
SELECT
TO_NUMBER('123.456E-40', '999.9999EEE', 38, 37)
FROM DUAL;
select
TO_NUMBER('12,345,678,912,345,678,912,345,678,912,345,678,912', '99,999,999,999,999,999,999,999,999,999,999,999,999', 38, 0)
from dual;
SELECT
TO_NUMBER(' 123.456E-40', '999.9999EEE', 38, 37)
FROM DUAL;
select
TO_NUMBER(' 12,345,678,912,345,678,912,345,678,912,345.678912', '99,999,999,999,999,999,999,999,999,999,999.999999', 38, 6)
from dual;
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0050 - INPUT EXPRESSION IS OUT OF THE RANGE '12.34567891234567891234567891234567891267+' ***/!!! CAST('12.34567891234567891234567891234567891267+' AS NUMBER(38, 18) , '99.999999999999999999999999999999999999S') FROM DUAL;
select
TO_NUMBER('12,345,678,912,345,678,912,345,678,912,345,678,912+', '99,999,999,999,999,999,999,999,999,999,999,999,999S', 38, 0)
from dual;
select
TO_NUMBER('12.48+', '99.99S', 38, 2)
from dual;
select
TO_NUMBER(' 12.48+', '99.99S', 38, 2)
from dual;
select
TO_NUMBER('12.48+ ', '99.99S', 38, 2)
from dual;
SELECT
TO_NUMBER('123.456+E-2', '999.9999SEEE', 38, 5)
FROM DUAL;
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0053 - INCORRECT INPUT FORMAT '123.456+E-2-' ***/!!! CAST('123.456+E-2-' AS NUMBER(38, 18) , '999.9999SEEE') FROM DUAL;
SELECT
TO_NUMBER('12356-', '99999S', 38, 0)
FROM DUAL;
select
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0050 - INPUT EXPRESSION IS OUT OF THE RANGE ' 1.0E+123' ***/!!! cast(' 1.0E+123' as NUMBER(38, 18) , '9.9EEEE') from dual;
select
TO_NUMBER('1.2E+02', 'FM9.9EEEE', 38, 0)
from dual;
select
TO_NUMBER('123.45', 'FM999.009', 38, 2)
from dual;
select
TO_NUMBER('123.00', 'FM999.009', 38, 2)
from dual;
select
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0045 - CAST TYPE L AND FML NOT SUPPORTED ***/!!! cast(' $123.45' as NUMBER(38, 18) , 'L999.99') from dual;
select
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0045 - CAST TYPE L AND FML NOT SUPPORTED ***/!!! cast('$123.45' as NUMBER(38, 18) , 'FML999.99') from dual;
select
TO_NUMBER('1234567890+', '9999999999S', 38, 0)
from dual;
Recommendations¶
No additional user actions are required.
If you need more support, you can email us at snowconvert-support@snowflake.com.