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, Jun 15 '16 at 18:00
@GordonLinoff What?, no, that's not right, op wants the opposite of `COALESCE` — Lamak, Jun 15 '16 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, Jun 15 '16 at 18:39

score 12 · Accepted Answer · answered Jun 15 '16 at 18:01

12

You can use NULLIF:

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

answered Jun 15 '16 at 18:01

Lamak

1 Answers1