MSCEWI2052

Snowflake implicit conversion to numeric differs from Teradata and may fail for non-literal strings

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

Both Teradata and Snowflake allow to pass string values to functions that expect numeric parameters, these strings are then parsed and converted to their numeric equivalent.

However there are differences on what the two languages consider a valid numeric string, Teradata is more permissive and successfully parses cases like empty / whitespace-only strings, embedded dashes, having no digits in the mantissa or exponent, currency signs, digit separators or specifying the sign of the number after the digits. For example, the following strings are valid:

• '1-2-3-4-5' -> 12345

• '$50' -> 50

• '5000-' -> -5000

• '1,569,284.55' -> 1569284.55

Snowflake applies automatic optimistic string conversion, expecting the strings to match either the TM9 or TME formats, so conversion fails for most of the cases mentioned. To solve these differences, Snowconvert processes string literals passed to functions that do an implicit conversion to numeric and generates equivalent strings that match TM9 or TME so they can be parsed by Snowflake. This only applies to literal string values, meaning non-literal values have no guarantee to be parsed by Snowflake.

Example code

Input code:

create table myTable(
    stringCol varchar(30)
);

insert into myTable values ('   1,236,857.45-');

select cos('   1,236,857.45-');

select cos(stringCol) from myTable;

Output code:

CREATE TABLE PUBLIC.myTable (
stringCol varchar(30)
);

INSERT INTO PUBLIC.myTable VALUES ('   1,236,857.45-');

SELECT
cos('-1236857.45');

SELECT
cos(stringCol /*** MSC-WARNING - MSCEWI2052 - SNOWFLAKE IMPLICIT CONVERSION TO NUMERIC DIFFERS FROM TERADATA AND MAY FAIL FOR NON-LITERAL STRING VALUES ***/)
FROM PUBLIC.myTable;

Recommendations

• No additional user actions are required.

• If you need more support, you can email us at [e-mail]