TSQL中REPLACE替换'NULL'为NULL时无匹配仍返回NULL的原因？

Ah, I see the issue here—let's break this down and fix it quickly!

The problem with using REPLACE([my_string], 'NULL', NULL) is a quirk of SQL's REPLACE() function: if any of the three parameters passed to it is NULL, the entire function returns NULL, regardless of what the other values are. Since you're passing NULL as the replacement value, every row gets NULL back, even when the original column doesn't contain the 'NULL' literal.

Luckily, there are two straightforward solutions to handle this correctly:

1. Use NULLIF() (Simplest Approach) #

The NULLIF() function is made exactly for this scenario—it compares two values, and returns NULL if they're equal; otherwise, it returns the first value. Perfect for swapping literal 'NULL' strings with actual NULL values:

-- Update the table to replace literal 'NULL' with real NULL
UPDATE #my_nulls
SET my_column = NULLIF(my_column, 'NULL');

2. Use a CASE Statement (More Flexible for Complex Scenarios) #

If you ever need to add more conditions later (like handling other literal values), a CASE statement gives you full control:

UPDATE #my_nulls
SET my_column = CASE
    WHEN my_column = 'NULL' THEN NULL -- Replace literal 'NULL' with NULL
    ELSE my_column -- Keep all other values as-is
END;

Verify the Fix #

Run a quick select to confirm the change worked as expected:

SELECT * FROM #my_nulls;

You'll see that rows 1 and 4 now have actual NULL values in my_column, while rows 2 and 3 retain their original text—exactly what you wanted!

内容的提问来源于stack exchange，提问作者DatumPoint