Dfyjkt

Is there a way to change all occurrences of a certain value within SQL regardless of column?

I have a table with ~200 columns which was imported from a text file. The NULL values came through as the string value 'NULL' and occur in most columns within the table. Is there a way to convert those values to true NULL values? I would like to avoid using UPDATE on each individual column is possible.

A single update may not be too painful:

update t
 set col1 = nullif(col1, 'NULL'),
 col2 = nullif(col2, 'NULL'),
 . . .;

You can generate the code in SQL or a spreadsheet by querying INFORMATION_SCHEMA.COLUMNS(or similar) for string columns.

You can use dynamic sql to build out the update script...

DECLARE @update_sql NVARCHAR(MAX) = N''

SELECT 
 @update_sql = CONCAT(@update_sql, N',
 mt.', c.name, N' = NULLIF(mt.', c.name, N', ''NULL'')') 
FROM
 sys.columns c
WHERE
 c.object_id = OBJECT_ID(N'dbo.MyTable')
 AND c.collation_name IS NOT NULL; -- easy way to make sure you're only looking at columns that can hold test data. 

SET @update_sql = CONCAT(N'
UPDATE mt SET',
STUFF(@update_sql, 1, 1, ''), N'
FROM 
 dbo.MyTable mt;')

PRINT(@update_sql);

You'll end up with output formatted like the following...

UPDATE mt SET
 mt.column_9 = NULLIF(mt.column_9, 'NULL'),
 mt.column_10 = NULLIF(mt.column_10, 'NULL'),
 mt.column_11 = NULLIF(mt.column_11, 'NULL'),
 mt.column_14 = NULLIF(mt.column_14, 'NULL'),
...
 mt.column_165 = NULLIF(mt.column_165, 'NULL'),
 mt.column_166 = NULLIF(mt.column_166, 'NULL'),
 mt.column_167 = NULLIF(mt.column_167, 'NULL'),
 mt.column_168 = NULLIF(mt.column_168, 'NULL')
FROM 
dbo.MyTable mt;

Note... The PRINT command is limited to 8000 characters of ASCII and 4000 characters of unicode. So, if you notice that the output script is being truncated, post back, I have a "long print" procedure that get around that limitation.

use the merge statement and set null for all matching rows which is fasters and efficient way to do it.

There is no way to do this without doing an update on each individual column.

There are shortcuts to writing such an update, like right-click>script as... or dynamic sql, but so far that's not what you've asked.