Menu
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.
457
In database terms, however, a null value is a value that doesn't exist: the field does not contain a value of any kind (not even a blank value). By contrast, a blank value is a real value: it just happens to be a string value containing 0 characters.
In particular, null values must be distinguished from blank values: A null database field means that there is no value for a given record. It indicates the absence of a value. A blank database field means that there is a value for a given record, and this value is empty (for a string value) or 0 (for a numeric value).
You also can specify the NULL keyword in the VALUES clause to indicate that a column should be assigned a NULL value. The following example inserts values into three columns of the orders table: INSERT INTO orders (orders_num, order_date, customer_num) VALUES (0, NULL, 123);
It is very important to understand that a NULL value is different than a zero value or a field that contains spaces, spaces are considered as values because they are strings (and sql can't tell what the value of a string means to the user per se), but a NULL signifies a missing value, and hence has no value associated ...
Use nullif
select nullif(Column1, '') from Orders
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
