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

Question

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.

geca · Accepted Answer · 2012-10-05 08:49:27Z

191

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.

answered Oct 5, 2012 at 8:49

geca

2,9812 gold badges20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Trisped Over a year ago

The major downside to this is that all rows in the table will be updated instead of just the rows which need the change.

tim Over a year ago

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

egrunin · Accepted Answer · 2010-09-20 03:06:47Z

63

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

edited Sep 20, 2010 at 3:06

answered Sep 13, 2010 at 15:26

egrunin

25.2k8 gold badges53 silver badges96 bronze badges

7 Comments

Bennor McCarthy Over a year ago

You don't even need to use RTRIM. SQL Server ignores trailing whitespace when comparing strings.

egrunin Over a year ago

@Bennor McCarthy: wow, I'm really slipping...going to drop that clause now

Bennor McCarthy Over a year ago

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. :)

MikeKulls Over a year ago

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.

egrunin Over a year ago

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

|

Bennor McCarthy · Accepted Answer · 2010-09-20 00:45:08Z

41

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

answered Sep 20, 2010 at 0:45

Bennor McCarthy

11.7k1 gold badge52 silver badges52 bronze badges

2 Comments

MikeKulls Over a year ago

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.

egrunin Over a year ago

I agree that the second query here really is the answer which best matches the question, even though my answer was the accepted one.

gls123 · Accepted Answer · 2016-08-30 12:29:39Z

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'

edited Aug 30, 2016 at 12:29

answered Mar 19, 2014 at 15:53

gls123

5,6472 gold badges30 silver badges30 bronze badges

2 Comments

GeorgesC Over a year ago

your script is great, also you can add: ` WHERE [' + COLUMN_NAME + '] ' + 'NOT LIKE ' + '''%[a-z,0-9]%''' `

gls123 Over a year ago

Thanks, what does that regex do?

craigh2 · Accepted Answer · 2010-09-13 15:30:07Z

13

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

answered Sep 13, 2010 at 15:30

craigh2

1312 bronze badges

Comments

user151323 · Accepted Answer · 2010-09-13 15:26:22Z

7

Maybe something like this?

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

answered Sep 13, 2010 at 15:26

user151323

Comments

DForck42 · Accepted Answer · 2010-09-13 15:57:25Z

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.

answered Sep 13, 2010 at 15:57

DForck42

20.5k14 gold badges61 silver badges86 bronze badges

1 Comment

MikeKulls Over a year ago

I think you've answered your own question as to why this is a bad idea.

Collectives™ on Stack Overflow

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

7 Answers 7

2 Comments

7 Comments

2 Comments

2 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

7 Comments

2 Comments

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related