如何使用 SQL Server 将空格转换为空值?

发布于 2024-09-18 18:10:45 字数 234 浏览 12 评论 0原文

我有一个表,该表上的列包含一些记录的空白空间。现在我需要将数据移动到另一个表并用 NULL 值替换空格。

我尝试使用:

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

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

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(7

贩梦商人 2024-09-25 18:10:46

你尝试过这个吗?

UPDATE table 
SET col1 = NULL 
WHERE col1 = ''

正如评论者指出的那样,您不必执行 ltrim()rtrim(),并且 NULL 列不会匹配 ''

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

世俗缘 2024-09-25 18:10:46

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

UPDATE table
SET col1 = NULL
WHERE col1 = ''

表中的 NULL 值将保持为 NULL,并且带有任何数字的仅空格字符的 col1 将更改为 NULL。

如果您想在从一个表复制到另一个表的过程中执行此操作,请使用以下命令:

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

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
百思不得你姐 2024-09-25 18:10:46

此代码生成一些 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'

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'
不回头走下去 2024-09-25 18:10:46

从源表中进行选择时,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

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
深海夜未眠 2024-09-25 18:10:46

也许是这样的?

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

Maybe something like this?

UPDATE [MyTable]
SET [SomeField] = NULL
WHERE [SomeField] is not NULL
AND LEN(LTRIM(RTRIM([SomeField]))) = 0
夜灵血窟げ 2024-09-25 18:10:46

这是给你的一个正则表达式。

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

本质上是找到其中没有字母或数字的任何列并将其设置为空。如果您的列仅包含特殊字符,则可能需要更新。

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.

删除会话 2024-09-25 18:10:45

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

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

来自 T-SQL 参考:< /a>

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

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文