How do I set column values to null where they are 'null' in TSQL?

Question

I've tried the following:

select 'update dbo.tableName set ' + sys.columns + ' = null where ' + sys.columns + ' = 'NULL'' from dbo.tableName

I imported a lot of excel data into ssms and the null values are actually varchars.

Answer 1

Basically, if you have multiple columns that have 'NULL' as a string literal in multiple different rows in the same table, you better so something like this:

UPDATE TableName
SET Col1 = NULLIF(Col1, 'NULL'),
    Col2 = NULLIF(Col2, 'NULL'),
    Col3 = NULLIF(Col3, 'NULL')
WHERE 'NULL' IN(Col1, Col2, Col3)

The NULLIF function will return NULL if both of it's arguments are the same, or the first argument otherwise.

If you want to do that dynamically for multiple tables, you can do something like this:

DECLARE @SQL nvarchar(max) = '';

SELECT  @SQL += 'UPDATE '+ TABLE_NAME + -- Update clause
        STUFF( -- Set clause start
        (
            SELECT ',' + COLUMN_NAME +  '= NULLIF('+ COLUMN_NAME +', ''NULL'')'
            FROM Information_schema.Columns C
            WHERE C.TABLE_NAME = T.TABLE_NAME
            FOR XML PATH('')    
        ), 1, 1, ' SET ') + -- Set clause end
        ' WHERE ''NULL'' IN(' + -- Where clause start
        STUFF(
        (
            SELECT ','+ COLUMN_NAME
            FROM Information_schema.Columns C
            WHERE C.TABLE_NAME = T.TABLE_NAME
            FOR XML PATH('')    
        ),1, 1, '')+ ');' -- Where clause end
FROM Information_schema.Tables T;

EXEC(@SQL);

You can see a live demo on rextester.