Snowflake implicit conversion to numeric differs from Teradata and may fail for non-literal strings
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.
create table myTable(
insert into myTable values (' 1,236,857.45-');
select cos(' 1,236,857.45-');
select cos(stringCol) from myTable;
CREATE TABLE PUBLIC.myTable (
INSERT INTO PUBLIC.myTable VALUES (' 1,236,857.45-');
cos(stringCol /*** MSC-WARNING - MSCEWI2052 - SNOWFLAKE IMPLICIT CONVERSION TO NUMERIC DIFFERS FROM TERADATA AND MAY FAIL FOR NON-LITERAL STRING VALUES ***/)
- No additional user actions are required.
- For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.