In Snowflake I have a column that holds a semi-structured data that should hold numbers but as of semi-structured data nature, that is not strict. I want to check the type of data and convert to float if possible. Something like that:

SELECT IFF(TYPEOF(column_name::data_name) is NUMBER, column_name::data_name::FLOAT, null)
FROM some_table

How can I make this query work?

CodePudding user response：

TRY_TO_DOUBLE is the tool you are looking for if you want it to be floating point. Otherwise if only Integer TRY_TO_NUMBER

SELECT try_to_double(column_name::data_name::TEXT)
FROM some_table

Here the DB doesn't like output of type VARAINT, which is what selecting a value from a VARAINT gives you. The way around this is turn it into a TEXT first:

SELECT 
    parse_json('{"data_name":3.14, "it_is_a_number":1234}') as column_name
    ,try_to_double(column_name:data_name::text) as dub
    ,try_to_number(column_name:it_is_a_number::text) as int
    ;