如何在SQL Server中将空格转换为null值？

Question

我有一个表格，其中某些记录的列包含空格。现在，我需要将数据移动到另一个表格，并用NULL值替换空格。

我尝试使用：

REPLACE(ltrim(rtrim(col1)),' ',NULL)

但它无效。 它将把col1的所有值转换为NULL。 我只想将那些空格为空的值转换为NULL。

- niceApp

Answer 1

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

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

根据T-SQL参考资料:

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

- geca

---

这样做的主要缺点是会更新表中所有行，而不仅仅是需要更改的那些行。 - Trisped

如果在列中存在非空字符串且您需要进行其他更新，或者在INSERT语句中使用它，则此选项是一个不错的选择。 - tim

Answer 2

你试过这个吗？

UPDATE table 
SET col1 = NULL 
WHERE col1 = ''

正如评论者指出的那样，你不必使用ltrim()或rtrim()函数，并且NULL列将不会匹配''。

- egrunin

---

即使不使用RTRIM函数，SQL Server在比较字符串时也会忽略末尾的空格。 - Bennor McCarthy

不是什么大问题。查询的性能或索引的使用并不是真正的关注点。无论如何，你的答案仍然是正确的。 :) - Bennor McCarthy

如果表中有禁止空字符串的约束（如果只使用null，则这是一个好主意），则此操作将失败。最好使用NULLIF函数在复制过程中删除空字符串。 - MikeKulls

@MikeKulls：他似乎暗示这个表没有这样的约束。个人而言，我总是尽量避免在数据记录中使用NULL，因为程序逻辑必须注意它们。 - egrunin

@egrunin：无论你怎么做，都应该有一个约束条件来确保你是一致的。他在这个表中使用了NULL，所以应该有一个约束条件来确保没有任何空字符串。即使他现在没有，未来也有可能添加，但只有在复制过程中完成才行。这是一种更简单、更快速的方法，因为它可以一步完成。 - MikeKulls

Answer 3

SQL Server在比较字符串时会忽略末尾的空格，所以' ' = ''。只需使用以下查询来更新即可：

UPDATE table
SET col1 = NULL
WHERE col1 = ''

你的表中的NULL值将保持不变，而仅由数字或空格组成的col1s将被更改为NULL。

如果您想在从一张表复制到另一张表的过程中执行此操作，请使用以下代码：

INSERT INTO newtable ( col1, othercolumn )
SELECT
   NULLIF(col1, ''),
   othercolumn
FROM table

- Bennor McCarthy

---

在我看来，这是最好的答案。问题要求在复制过程中如何将空字符串转换为null。被接受的答案并不好，因为它需要分两步完成，这不仅效率低下，而且会阻止数据库管理员添加约束以确保没有空字符串存在。 - MikeKulls

我同意这里的第二个查询确实是最符合问题的答案，尽管我的回答被接受了。 - egrunin

Answer 4

这段代码可以在数据库中的每个表和列上生成一些 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'

- gls123

---

你的脚本很棒，你还可以添加： WHERE [' + COLUMN_NAME + '] ' + 'NOT LIKE ' + '''%[a-z,0-9]%''' - GeorgesC

谢谢，那个正则表达式是做什么用的？ - gls123

Answer 5

使用 case 语句可以在选择源表时解决问题：

CASE
  WHEN col1 = ' ' THEN NULL
  ELSE col1
END col1

还有一件需要注意的事情是，您的LTRIM和RTRIM将空格（' '）的值减少到空白（''）。如果您需要删除空格，则应相应地修改CASE语句：

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

- craigh2

Answer 6

也许是这样的吗？

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

- user151323

Answer 7

这里有一个正则表达式。

update table
set col1=null
where col1 not like '%[a-z,0-9]%'

本质上查找任何不含字母或数字的列，并将其设置为null。如果您的列仅包含特殊字符，则可能需要进行更新。

- DForck42

---

我认为你已经自己回答了为什么这是个不好的想法的问题。 - MikeKulls