74

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.

Leigh
  • 28,765
  • 10
  • 55
  • 103
niceApp
  • 2,913
  • 8
  • 35
  • 36

7 Answers7

169

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.

geca
  • 2,711
  • 2
  • 17
  • 26
  • 7
    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 05 '14 at 22:58
  • This is a good option if you're also making other updates to the column when there's a non-empty string, or if you're using it in an `INSERT` statement – tim Feb 02 '21 at 17:31
58

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

egrunin
  • 24,650
  • 8
  • 50
  • 93
  • 10
    You don't even need to use RTRIM. SQL Server ignores trailing whitespace when comparing strings. – Bennor McCarthy Sep 20 '10 at 00:47
  • @Bennor McCarthy: wow, I'm really slipping...going to drop that clause now – egrunin Sep 20 '10 at 03: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 03: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 06 '12 at 03:28
  • @MikeKulls: he seems to imply that that the table has no such constraints. Personally I always try to avoid `NULL` in data records, because then the program logic has to watch out for them. – egrunin Jul 06 '12 at 03:43
  • @egrunin: Either way you do it you should have a constraint to ensure you are consistant. He is using NULL in this table so should have a constraint to ensure nothing is an empty string. Even if he doesn't currently there is the chance it could be added in the future but only if it is done during the copy. This is a simpler faster way to do it anyway because it is done in one step. – MikeKulls Jul 08 '12 at 21:39
40

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
Bennor McCarthy
  • 11,415
  • 1
  • 49
  • 51
  • 2
    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 06 '12 at 03: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 '14 at 09:06
17

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

SELECT
   'UPDATE ['+T.TABLE_SCHEMA+'].[' + T.TABLE_NAME + '] SET [' + COLUMN_NAME + '] = NULL 
   WHERE [' + COLUMN_NAME + '] = '''''
FROM 
    INFORMATION_SCHEMA.columns C
INNER JOIN
    INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME=T.TABLE_NAME AND C.TABLE_SCHEMA=T.TABLE_SCHEMA
WHERE 
    DATA_TYPE IN ('char','nchar','varchar','nvarchar')
AND C.IS_NULLABLE='YES'
AND T.TABLE_TYPE='BASE TABLE'
gls123
  • 5,467
  • 2
  • 28
  • 28
12

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
craigh2
  • 121
  • 2
7

Maybe something like this?

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

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.

DForck42
  • 19,789
  • 13
  • 59
  • 84