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
.
This code generates some SQL which can achieve this on every table and column in the database:
here's a regex one for ya.
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.
A case statement should do the trick when selecting from your source table:
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:
SQL Server ignores trailing whitespace when comparing strings, so ' ' = ''. Just use the following query for your update
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:
I solved a similar problem using
NULLIF
function:From the T-SQL reference:
Maybe something like this?