Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table that contains NULL values but the problem is that some of the values are actually string "NULL" and not actual NULLS so when you trying something like

where date is null

it will not return the fields that have the "NULL" string.

What I am needing to do is run an update of the whole table that will convert all string "NULLS" to the actual NULL value. The "NULL" strings happen throughout all columns of the table so it is not just 1 column that needs to be updated. I am not sure how to approach this scenario. I'm thinking I might need to use a loop since i have many columns but then again there might be a simple solution without having to use a loop. What would be the best way to resolve this issue?

share|improve this question
Are we talking about 4-5 columns? Or about 100 columns? – ypercubeᵀᴹ Apr 18 '13 at 18:39
more like 30 to 40 columns – PokerPlayer23 Apr 18 '13 at 18:41
up vote 8 down vote accepted

SET @sql = N'';

SELECT @sql = @sql + '
  ' + QUOTENAME(name) + ' = CASE
  WHEN ' + QUOTENAME(name) + ' = ''NULL'' THEN NULL ELSE '
  + QUOTENAME(name) + ' END,'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.YourTableName')
AND system_type_id IN (35,99,167,175,231,239);

SELECT @sql = N'UPDATE dbo.YourTableName SET ' + LEFT(@sql, LEN(@sql)-1) + ';';

PRINT @sql;
--EXEC sp_executesql @sql;
share|improve this answer
that worked perfectly! thanks! – PokerPlayer23 Apr 18 '13 at 18:57
@Aaron, where could we find the lookup table for the system_type_id? Thanks in advance. – Travis Gan Apr 18 '13 at 20:21
They're in sys.types – Aaron Bertrand Apr 18 '13 at 20:22
@Aaron, maybe I am not in the right page. This is the MSDN page ( I was looking at, but it doesn't have the list of system_type_id and its corresponding data type. – Travis Gan Apr 18 '13 at 20:24
@Travis the documentation does not list all of the values, it just describes the view itself. If you want to see the values, just run a select: SELECT * FROM sys.types; – Aaron Bertrand Apr 18 '13 at 20:25

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.