user1589188 user1589188 - 1 year ago 46
SQL Question

SQL REPLACE with NULL unexpected result

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

SELECT REPLACE('abc', '1', NULL)


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:

SELECT 
    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:

SELECT
    CASE 
        WHEN 'abc' LIKE '%1%' THEN NULL
        ELSE 'abc'
    END