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.
Use nullif
select nullif(Column1, '') from Orders
NULLIF will return NULL if the value is equal to the second argument.
NULLIF matching on an empty string returns NULL even if the column value has any number of whitespace characters (e.g. '', ' ', ' ').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)
'' is equal to ' ' seems confusing to me. I prefer to be explicit about such logic.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, '').
declare @Blank nvarchar(1)=''
SELECT nullif(COLUMN1,@Blank)
FROM Orders;
Its select all COLUMN1 records as null if thay are blank.
