SSC-EWI-TD0052

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

Severity

Low

Description

Both Teradata and Snowflake allow string values to function 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:

IN -> Teradata_01.sql

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:

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE myTable (
    stringCol varchar(30)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"teradata"}}'
;

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

SELECT
    cos('-1236857.45');

SELECT
    cos(stringCol !!!RESOLVE EWI!!! /*** SSC-EWI-TD0052 - SNOWFLAKE IMPLICIT CONVERSION TO NUMERIC DIFFERS FROM TERADATA AND MAY FAIL FOR NON-LITERAL STRING VALUES ***/!!!)
from
    myTable;

Recommendations

• No additional user actions are required.

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