user1589188 user1589188 - 7 months ago 15
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

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