Search code examples
sql-servernullis-empty

How to convert empty spaces into null values, using SQL Server?

I have a table and the columns on this table contains empty spaces for some records. Now I need to move the data to another table and replace the empty spaces with a NULL value.

I tried to use:

REPLACE(ltrim(rtrim(col1)),' ',NULL)

but it doesn't work. It will convert all of the values of col1 to NULL. I just want to convert only those values that have empty spaces to NULL.

Solution

  • 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 ''.