How to get the column name that failed a type conversion on Snowflake

0
votes

Consider the following SQL:

SELECT
  value::number,
  discount::number
FROM data

Consider that there is one row where either value or discount has the value 002:23, which can't be converted to number. Snowflake would then fail with an error similar to:

100038 (22018): 01902838-0221-536e-0000-9ef90010d4d6: Numeric value '002:36' is not recognized

Is there any way to know which column this failed value came from? This would immensely help debugging a table with dozens of columns being parsed into numbers.

2
snowflake-cloud-data-platform

2 Answers

1
votes

This is how I do it, I'd test for each of the columns to see where things go bad.

I hope this helps...Rich

SELECT  value
FROM    data
WHERE   value IS NOT NULL 
AND     TRY_TO_NUMBER(value) IS NULL;
0
votes

You can use TRY_CAST instead of ::number for conversion which will return NULL instead of throwing an error when the value cannot be converted:

SELECT TRY_CAST(value AS NUMERIC) AS "value", TRY_CAST(discount AS NUMERIC) AS "discount" FROM data

For more details: https://docs.snowflake.net/manuals/sql-reference/functions/try_cast.html