Replace null string with null value

Question

I have a table that has a string value of 'null' that I wish to replace with an actual NULL value.

However if I try to do the following in my select

Select Replace(Mark,'null',NULL) from tblname

It replaces all rows and not just the rows with the string. If I change it to

Select Replace(Mark,'null',0) from tblname

It does what I would expect and only change the ones with string 'null'

Always specify (tag) the actual database you are using when you ask SQL questions. — sstan
– sstan, Commented Jun 15, 2016 at 18:00
@GordonLinoff What?, no, that's not right, op wants the opposite of COALESCE — Lamak
– Lamak, Commented Jun 15, 2016 at 18:37
+1 for showing me something I had overlooked in the doco: "REPLACE returns NULL if any one of the arguments is NULL." It doesn't seem very logical when some values of Mark may not contain substring 'null'. — John D
– John D, Commented Jun 15, 2016 at 18:39

Lamak · Accepted Answer · 2016-06-15 18:01:50Z

13

You can use NULLIF:

SELECT NULLIF(Mark,'null') 
FROM tblname;

answered Jun 15, 2016 at 18:01

Lamak

70.8k12 gold badges118 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1