I have a case statement to rectify one business logic in snowflake:

INSERT INTO DB.table_b 
    SELECT
        CASE 
            WHEN UPPER(emp) <> LOWER(emp) THEN NULL
            WHEN emp IS NULL THEN nullif(emp, 'NULL')
            ELSE emp  
        END AS emp_no 
    FROM
        DB.table_a;

The 'table_a' content as below :

emp
-------
ABCD
NULL
''
23

It contains character string, null, empty and numbers. So, the requirement is to take only numbers and empty values from the case statement since the column emp_no in 'table_b' is numeric type. In source table if the column value is string then we have to insert NULL value. But as the 'table_b' column is of type 'numeric' the null value is not getting inserted and getting following error

Numeric value '' is not recognized

CodePudding user response：

Using TRY_TO_NUMBER:

A special version of TO_DECIMAL , TO_NUMBER , TO_NUMERIC that performs the same operation (i.e. converts an input expression to a fixed-point number), but with error-handling support (i.e. if the conversion cannot be performed, it returns a NULL value instead of raising an error).

INSERT INTO DB.table_b 
SELECT TRY_TO_NUMBER(emp) AS emp
FROM DB.table_a;

CodePudding user response：

you can not use IS_INTEGER but for VARCHAR(16777216) it isn't supported

So a regular expression would be better

INSERT INTO DB.table_b 
    SELECT
        CASE 
            WHEN regexp_like(emp,'^[0-9] $') THEN emp
            ELSE NULL  
        END AS emp_no 
    FROM
        DB.table_a;

CodePudding user response：

As Lukasz mentions you should use the TRY_TO_x functions (TRY_TO_NUMERIC, TRY_TO_DOUBLE) as these safely handle parsing the types, and return NULL if the parse fails. The extra note I will add is that both NUMBER/NUMERICs and DOUBLEs will parse 0.1234 but get different results, which you didn't mention as caring about, but I think is worth noting, so I am adding an extra answer to point the difference out.

The CTE is just to get the values into the SQL:

WITH data(emp) as (
    select * from values
        ('ABCD'),
        (NULL),
        (''),
        ('0.123'),
        ('23')
)
SELECT emp
    ,try_to_numeric(emp) as emp_as_num
    ,try_to_double(emp) as emp_as_float
FROM data