user1589188 user1589188 - 1 year ago 61
SQL Question

SQL REPLACE with NULL unexpected result

I want to replace certain varchar to NULL. Straight forwardly I used


i.e. for every input varchar if there is at least a '1' within I want the whole input becomes NULL (e.g. '123' is NULL, 'abc-1' is NULL, etc.)

Whats bugging me is even when there is no match (e.g. no need to replace), it still gives me NULL. For instance running the statement above gives you NULL.

Why? And any workaround?

Answer to be picked with the "why" component and a nice simple workaround.

Answer Source

According to the REPLACE documentation:

Returns NULL if any one of the arguments is NULL.

Based from the above, this query will return NULL for all columns:

    REPLACE(NULL, 'abc', 'a'),
    REPLACE('abc', NULL, 'a'),
    REPLACE('abc', 'a', NULL)

If you want to return NULL for every input that contains the '1', you use a CASE expression:

        WHEN 'abc' LIKE '%1%' THEN NULL
        ELSE 'abc'