sql - Using try_cast in snowflake to deal with very long numbers

Question

I'm using try_cast in snowflake to convert any long values in sql to NULL.

Here is my code:

When I try running the above code, I'm getting the error as below:

I'm flattening a JSON array and using try_cast to make any large values to NULL because I was getting an error Failed to cast variant value {numberLong: -8301085358432}

SELECT try_cast(item.value:price) as item_price,
       try_cast(item.value:total_price_bill) as items_total_price
FROM table, LATERAL FLATTEN(input => products) item

Error:

SQL compilation error error at line 1 at position ')'.

I don't understand where I'm doing wrong

question from:https://stackoverflow.com/questions/65923578/using-try-cast-in-snowflake-to-deal-with-very-long-numbers

Answer 1

you are using wrong syntax for try_cast. according to snowflake documentations the syntax is :

TRY_CAST( <source_string_expr> AS <target_data_type> )

and also note:

1. Only works for string expressions.
2. target_data_type must be one of the following:
   • VARCHAR (or any of its synonyms)
   • NUMBER (or any of its synonyms)
   • DOUBLE
   • BOOLEAN
   • DATE
   • TIME
   • TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, or TIMESTAMP_TZ

so for example you have to have something like this if item.value:price is string:

select try_cast(item.value:price as NUMBER) as item_price,
....