SQL TSQL 中的空字符字面量是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2828333/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the Null Character literal in TSQL?
提问by t3rse
I am wondering what the literal for a Null character(e.g. '\0') is in TSQL.
我想知道TSQL 中空字符（例如“\0”）的文字是什么。
Note:not a NULL field value, but the null character (see link).
注意：不是 NULL 字段值，而是空字符（见链接）。
I have a column with a mix of typical and a null character. I'm trying to replace the null character with a different value. I would have thought that the following would work but it is unsuccessfull:
我有一列混合了典型字符和空字符。我正在尝试用不同的值替换空字符。我原以为以下方法可行，但未成功：
select REPLACE(field_with_nullchar, char(0), ',') from FOO where BAR = 20
回答by Alex
There are two different behaviors in the Cade Roux's answer: replacement is successful (when SQL collation is used) and unsuccessful (Windows collation is used). The reason is in type of collation used.
Cade Roux 的回答中有两种不同的行为：替换成功（使用 SQL 排序规则时）和不成功（使用 Windows 排序规则）。原因在于使用的归类类型。
This behaviour was submittedto Microsoft nearly 4 years ago:
这种行为是在近 4 年前提交给微软的：
Q:When trying a replace a NUL character with replace(), this works is the value has an SQL collation, but not a Windows collation.
A:This is due to the fact that 0x0000 is an undefined character in Windows collations. All undefined characters are ignored during comparison, sort, and pattern matching. So searing for 'a' + char(0) is really searching for ‘a', and searching for char(0) is equivalent to empty string.
The way to handle undefined character is a bit confusing, but this is the way that Windows defined to sort them, and SQL Server conforms with the general Windows API.
In SQL collation, there is no notion of undefined character. Each code point is assigned a weight, that's why we don't see a problem there.
问：当尝试用 replace() 替换 NUL 字符时，这是有效的，因为该值具有 SQL 排序规则，但不是 Windows 排序规则。
答：这是因为 0x0000 在 Windows 排序规则中是一个未定义的字符。在比较、排序和模式匹配期间，所有未定义的字符都将被忽略。所以searing for 'a' + char(0) 实际上是在搜索'a'，而搜索char(0) 就相当于空字符串。
处理未定义字符的方式有点混乱，但这是 Windows 定义的对它们进行排序的方式，SQL Server 符合通用的 Windows API。
在 SQL 排序规则中，没有未定义字符的概念。每个代码点都分配了一个权重，这就是为什么我们在那里看不到问题。
but unfortunately, it is still undocumented.
但不幸的是，它仍然没有记录。
So, it seems the only one solution is to change collation to SQL collation (e.g. SQL_Latin1_General_CP1_CI_ASmay be used as well).
因此，似乎唯一的解决方案是将排序规则更改为 SQL 排序规则（例如SQL_Latin1_General_CP1_CI_AS也可以使用）。
*I removed my previous answer as unnecessary
*我删除了我之前的回答，因为没有必要
回答by Cade Roux
Looks like the C-style terminator is a terminator in SQL as well:
看起来 C 风格的终止符也是 SQL 中的终止符：
SELECT REPLACE(bad, CHAR(0), ' ')
FROM (
SELECT 'a' + CHAR(0) + 'b' AS bad
) AS X
Looks like it's also dependent on COLLATION:
看起来它也依赖于 COLLATION：
SELECT REPLACE(CAST(bad COLLATE SQL_Latin1_General_CP1_CI_AS AS varchar(10)), CHAR(0), ' ')
FROM (
SELECT 'a' + CHAR(0) + 'b' AS bad
) AS X
works as expected, compared to:
与预期相比，工作正常：
SELECT REPLACE(CAST(bad COLLATE Latin1_General_CI_AS AS varchar(10)), CHAR(0), ' ')
FROM (
SELECT 'a' + CHAR(0) + 'b' AS bad
) AS X
回答by Alex K.
A VARBINARYcast should work with any collation
一VARBINARY投应与任何整理工作
SELECT
REPLACE(CAST(CAST(fld AS VARCHAR(5)) AS VARBINARY(5)), 0x0, ',')
FROM
(SELECT 'QQ' + CHAR(0) + 'WW' COLLATE Latin1_General_CI_AS AS fld) AS T
SELECT
REPLACE(CAST(CAST(fld AS VARCHAR(5)) AS VARBINARY(5)), 0x0, ',')
FROM
(SELECT 'QQ' + CHAR(0) + 'WW' COLLATE SQL_Latin1_General_CP1_CI_AS AS fld) AS T
>>QQ,WW
>>QQ,WW
回答by Niederee
I was having the same issue and using nullifsolved it for me.
我遇到了同样的问题，使用nullif它为我解决了。
Select nullif(field_with_nullchar,'') from FOO where BAR = 20
回答by Blorgbeard is out
Are you certain they are null characters? How did you get them in there?
你确定它们是空字符吗？你是怎么把他们弄进去的？
It looks like SQL Server treats them as string terminators. This query:
看起来 SQL Server 将它们视为字符串终止符。这个查询：
select 'aaa' + char(0) + 'bbb'
Returns aaafor me (on SQL Server 2008).
aaa为我返回（在 SQL Server 2008 上）。
Edit:Above is wrong - it's just the results grid that treats them that way. They show up in text mode.
编辑：以上是错误的 - 只是结果网格以这种方式对待它们。它们以文本模式显示。
回答by Tom H
I just ran the test below on my server (2008) and it was successful. It may have to do with an ANSI setting. I'll try flipping some settings here and see if I can reproduce your issue.
我刚刚在我的服务器（2008）上运行了下面的测试，它成功了。它可能与 ANSI 设置有关。我会尝试在这里翻转一些设置，看看我是否可以重现您的问题。
DECLARE @test_null_char VARCHAR(20)
SET @test_null_char = 'aaa' + CHAR(0) + 'bbb'
SELECT @test_null_char -- Returns "aaa bbb"
SET @test_null_char = REPLACE(@test_null_char, CHAR(0), 'ccc')
SELECT @test_null_char -- Returns "aaacccbbb"
