Snowflake: Insert null value in a numeric type column

Question

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

Lukasz Szozda · Accepted Answer · 2022-06-26 14:18:13Z

2

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;

answered Jun 26, 2022 at 14:18

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nbk · Accepted Answer · 2022-06-26 12:20:04Z

1

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;

edited Jun 26, 2022 at 12:20

answered Jun 26, 2022 at 11:58

nbk

49.6k8 gold badges32 silver badges53 bronze badges

2 Comments

Rocky1989 Over a year ago

Thanks for the response, Getting following error: Invalid argument types for function 'IS_INTEGER': (VARCHAR(16777216))

nbk Over a year ago

that is a big varchar i will rewrite the query to regular expressions

Simeon Pilgrim · Accepted Answer · 2022-06-26 22:06:59Z

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

EMP	EMP_AS_NUM	EMP_AS_FLOAT
'ABCD'	null	null
null	null	null
''	null	null
'0.123'	0	0.123
'23'	23	23

reachbgk · Accepted Answer · 2022-06-27 16:19:36Z

1

You can test for amp being string and set the string to NULL. Only numeric values will go into the second case statement.

SELECT
        CASE 
            WHEN IS_VARCHAR(emp) then NULL else
                case WHEN UPPER(emp) <> LOWER(emp) THEN NULL ELSE emp end
        end AS emp_no

answered Jun 27, 2022 at 16:19

reachbgk

931 gold badge1 silver badge10 bronze badges

Collectives™ on Stack Overflow

Snowflake: Insert null value in a numeric type column

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related