sql - 雪花: Insert null value in a numeric type column

Question

我有一个案例语句来纠正雪花中的一个业务逻辑:

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;

'table_a'内容如下:

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

包含字符串、null、空和数字。因此，要求是从 case 语句中仅获取数字和空值，因为“table_b”中的列 emp_no 是数字类型。在源表中，如果列值是字符串，那么我们必须插入NULL值。但由于“table_b”列的类型为“数字”，因此未插入空值并出现以下错误

Numeric value '' is not recognized

Answer 1

使用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;