How to replace empty cell with NULL on entire table/multiple columns

[omar999]

hi guys

I hope the thread title is self explanatory but im basically I've got a rather large ms sql table which is populated with data taken from an excel spreadsheet.

Problem is that there are a lot of cells all over the table within several columns that are empty whereas others contain the NULL value.

Is there a way to replace all of these empty cells with the NULL value??

so something like

select * from TABLE_NAME
REPLACE '' WITH NULL

thanks in advance
Omar.

[viktorka]

update TABLE_NAME set ColumnName=NULL where ColumnName=''

[Mark Kremers]

Try this one. It will generate all the update statements for 1 database, without any objects in the sys scheme.

select  replace(replace('update [TABLE] set [COLUMN] = NULL where len([COLUMN]) = 0', '[TABLE]', t.name), '[COLUMN]', c.name)
from    sys.columns c
        inner join sys.tables t
          on  t.object_id = c.object_id
          and t.schema_id <> 4