According to Snowflake documentation, TRY_TO_NUMBER should return NULL when passed a non-numeric value. However, when passing the string 'E', the function returns a 0.
SELECT TRY_TO_NUMBER('E');
2 Answers
because there is an implicit zero in front and after it:
SELECT TRY_TO_NUMBER('E'),
TRY_TO_NUMBER('1E2'),
TRY_TO_NUMBER('0E'),
TRY_TO_NUMBER('0E0');
gives:
| TRY_TO_NUMBER('E') | TRY_TO_NUMBER('1E2') | TRY_TO_NUMBER('0E') | TRY_TO_NUMBER('0E0') |
|---|---|---|---|
| 0 | 100 | 0 | 0 |
probably much like 010.0 and 10 and 10.0` all parse to the same thing, the zero's are not required.
But it does seems like a failure of the parser.
This can be overcome by passing an optional format parameter 'TM9' which specifies that you're NOT expecting scientific notation.
SELECT TRY_TO_NUMBER('E','TM9'),
TRY_TO_NUMBER('1E2','TM9'),
TRY_TO_NUMBER('0E','TM9'),
TRY_TO_NUMBER('0E0','TM9')
All of those now successfully return NULL, as I'm assuming you want them to.
Related