Snowflake: Insert null value in a numeric type column-CodePudding

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;