convert empty space to null

Question

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.

Answer 1

Maybe something like this?

UPDATE [MyTable]
SET [SomeField] = NULL
WHERE [SomeField] is not NULL
AND LEN(LTRIM(RTRIM([SomeField]))) = 0

Answer 2

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

Answer 3

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

CASE
  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:

CASE
  WHEN LTRIM(RTRIM(col1)) = '' THEN NULL
  ELSE LTRIM(RTRIM(col1))
END col1

Answer 4

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.

Answer 5

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 )
SELECT
   NULLIF(col1, ''),
   othercolumn
FROM table