How to convert empty spaces into null values, using SQL Server?
I solved a similar problem using NULLIF function:
UPDATE table SET col1 = NULLIF(col1, '')NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.
Did you try this?
UPDATE table SET col1 = NULL WHERE col1 = ''As the commenters point out, you don't have to do ltrim() or rtrim(), and NULL columns will not match ''.
SQL Server ignores trailing whitespace when comparing strings, so ' ' = ''. Just use the following query for your update
UPDATE tableSET col1 = NULLWHERE col1 = ''NULL values in your table will stay NULL, and col1s with any number on space only characters will be changed to NULL.
If you want to do it during your copy from one table to another, use this:
INSERT INTO newtable ( col1, othercolumn )SELECT NULLIF(col1, ''), othercolumnFROM table