Take the 2-minute tour ×

I am 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 NULL value. I tried to use

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

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

Thank you in advance for your help.

share|improve this question
Why did you tag this question with [oracle]? –  APC Sep 13 '10 at 15:26
because oracle and sql server queries are similar. So, if a query works for oracle that can be implemented on sql server too. –  niceApp Sep 13 '10 at 15:44
@niceApp, that's not necessarily true about ORACLE and Sql Server. They are similar yes, but not the same. Your question would in fact, be meaningless for Oracle, because Oracle treats empty string as NULL, so you would just need to trim it and you'd be done. –  eidylon Sep 16 at 16:35

7 Answers 7

up vote 12 down vote accepted

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

share|improve this answer
@Martin Smith: I was wrong. Edit appended. –  egrunin Sep 20 '10 at 0:10
You don't even need to use RTRIM. SQL Server ignores trailing whitespace when comparing strings. –  Bennor McCarthy Sep 20 '10 at 0:47
@Bennor McCarthy: wow, I'm really slipping...going to drop that clause now –  egrunin Sep 20 '10 at 3:04
Not really a major problem. It's not like performance or use of indexes is really a concern for the query. Your answer was still correct anyway. :) –  Bennor McCarthy Sep 20 '10 at 3:18
This will fail if the table has a constraint to disallow empty strings (which is a good idea if you are using only nulls). It's better to remove the empty strings during the copy with the NULLIF function. –  MikeKulls Jul 6 '12 at 3:28

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.

share|improve this answer
The major downside to this is that all rows in the table will be updated instead of just the rows which need the change. –  Trisped Dec 5 at 22:58

SQL Server ignores trailing whitespace when comparing strings, so ' ' = ''. Just use the following query for your update

UPDATE table
SET col1 = NULL
WHERE 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 )
   NULLIF(col1, ''),
FROM table
share|improve this answer
IMO this is the best answer. The question asked how to convert the empty strings to null during the copy. The accepted answer is not good because it does it in 2 steps which is not only less efficient but stops the DBA adding a constraint to ensure there are no empty strings. –  MikeKulls Jul 6 '12 at 3:24
I agree that the second query here really is the answer which best matches the question, even though my answer was the accepted one. –  egrunin Jul 29 at 9:06

Maybe something like this?

UPDATE [MyTable]
SET [SomeField] = NULL
WHERE [SomeField] is not NULL
AND LEN(LTRIM(RTRIM([SomeField]))) = 0
share|improve this answer

A case statement should do the trick when selecting from your source table:

  WHEN col1 = ' ' THEN NULL
  ELSE col1
END col1

Also, one thing to note is that your LTRIM and RTRIM reduce the value from a space (' ') to blank (''). If you need to remove white space, then the case statement should be modified appropriately:

END col1
share|improve this answer

This code generates some SQL which can achieve this on every table and column in the database:

   WHERE [' + COLUMN_NAME + '] = '''''
    DATA_TYPE IN ('char','nchar','varchar','nvarchar')
share|improve this answer

here's a regex one for ya.

update table
set col1=null
where col1 not like '%[a-z,0-9]%'

essentially finds any columns that dont have letters or numbers in them and sets it to null. might have to update if you have columns with just special characters.

share|improve this answer
I think you've answered your own question as to why this is a bad idea. –  MikeKulls Jul 6 '12 at 3:19

Your Answer


