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

sql-server null is-empty
niceApp picture niceApp · Sep 13, 2010 · Viewed 183.6k times · Source

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.

Answer

geca picture geca · Oct 5, 2012

I solved a similar problem using NULLIF function:

UPDATE table 
SET col1 = NULLIF(col1, '')

From the T-SQL reference:

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.

Related questions

Altering a column: null to not null

I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing nulls to 0, data must …

Read More
How to use NULL or empty string in SQL

I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.

Read More
Not equal <> != operator on NULL

Could someone please explain the following behavior in SQL? SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)

Read More