How to convert empty spaces into null values, using SQL Server?
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 N开发者_开发百科ULL
.
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.
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 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
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'
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
Maybe something like this?
UPDATE [MyTable]
SET [SomeField] = NULL
WHERE [SomeField] is not NULL
AND LEN(LTRIM(RTRIM([SomeField]))) = 0
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.
精彩评论