CODEWITHSUNDEEP
sqlsql-server
user3004110
user3004110

Reputation: 969

How to replace empty spaces with NULL

I have a column in sql server 2012 which contain white spaces. I want to replace these empty spaces with NULL. I have written the following query but its not working.

SELECT replace(COLUMN1, '',NULL) 
FROM Orders;

How to achieve the above functionality. Thanks in advance.

Upvotes: 17

Views: 70684

Answers (4)

kapil
kapil

Reputation: 11

declare @Blank nvarchar(1)=''

SELECT nullif(COLUMN1,@Blank) 
FROM Orders;

Its select all COLUMN1 records as null if thay are blank.

Upvotes: 0

RaghvendraSingh
RaghvendraSingh

Reputation: 21

The query below:

select REPLACE(ColumnName,'',NULL)as tb from TableName

can not be used as it returns all value of this column with NULL only. Because if any parameter of Replace function is null then it returns only NULL.

It can be advisable to use NULLIF(columnName, '').

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

If you want to handle the situation where the value consists of spaces or has zero length, then use ltrim() or like:

 select (case when value like '%[^ ]%' then value end)

Similarly,

select (case when ltrim(value) <> '' then value end)

Upvotes: 2

podiluska
podiluska

Reputation: 51494

Use nullif

select nullif(Column1, '') from Orders

Upvotes: 43

Related Questions