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

User · Answer

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

User · Answer

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

User · Answer

Maybe something like this   UPDATE  MyTable  SET  SomeField    NULL WHERE  SomeField  is not NULL AND LEN LTRIM RTRIM  SomeField       0

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] How to convert empty spaces into null values, using SQL Server?

Examples related to sql-server

Examples related to null

Examples related to is-empty