patternsqlMinor

Update "NULL" string to Actual NULL value

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

updateactualnullvaluestring

Problem

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?

Solution

DECLARE @sql NVARCHAR(MAX);

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;

Code Snippets

DECLARE @sql NVARCHAR(MAX);

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;

Context

StackExchange Database Administrators Q#40395, answer score: 8

Revisions (0)

No revisions yet.