如何使用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 NULL.

但它不工作。它将col1的所有值转换为NULL。我只想把那些空空间的值转换为NULL。

7 个解决方案

#1

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 ''.

正如评论者指出的，您不需要做ltrim()或rtrim()，空列将不匹配”。

#2

I solved a similar problem using NULLIF function:

我用NULLIF函数解决了一个类似的问题:

UPDATE table 
SET col1 = NULLIF(col1, '')

From the T-SQL reference:

从t - sql参考:

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.

如果两个表达式不相等，则NULLIF返回第一个表达式。如果表达式相等，则NULLIF返回第一个表达式类型的空值。

#3

SQL Server ignores trailing whitespace when comparing strings, so ' ' = ''. Just use the following query for your update

SQL Server在比较字符串时忽略尾随空格，所以'' = "。只需使用以下查询进行更新

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.

表中的空值将保持为空，只有空格上的任何数字的col1将被更改为空。

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

#4

This code generates some SQL which can achieve this on every table and column in the database:

这段代码生成了一些SQL，可以在数据库中的每个表和列上实现这一点:

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'

#5

A case statement should do the trick when selecting from your source table:

当从源表中进行选择时，case语句应该起到这个作用:

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:

另外，需要注意的一点是，您的LTRIM和RTRIM将空间('')的值降低为空白(")。如果需要删除空格，则应适当修改case语句:

CASE
  WHEN LTRIM(RTRIM(col1)) = '' THEN NULL
  ELSE LTRIM(RTRIM(col1))
END col1

#6

Maybe something like this?

也许是这样的?

UPDATE [MyTable]
SET [SomeField] = NULL
WHERE [SomeField] is not NULL
AND LEN(LTRIM(RTRIM([SomeField]))) = 0

#7

-1

here's a regex one for ya.

这是给你的regex。

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.

本质上，找到任何没有字母或数字的列，并将其设置为null。如果只有特殊字符的列，可能需要更新。

#1