Question 105854 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – Snowflake: Insert null value in a numeric type column

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

Tags:

4

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
  4. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search