TSQL中的空字符是什么?

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

注意:不是空字段值，而是空字符(参见链接)。

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

6 个解决方案

#1

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 submitted to 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.

问:当尝试用replace()替换NUL字符时，这是可行的，因为该值具有SQL排序规则，而不是Windows排序规则。

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.

答:这是因为0x0000是Windows排序中的一个未定义字符。在比较、排序和模式匹配过程中，所有未定义字符都被忽略。所以搜索'a' + char(0)实际上是在搜索'a'，而搜索char(0)等同于空字符串。

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.

处理未定义字符的方式有点混乱，但这是Windows定义的对它们进行排序的方式，SQL Server符合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.

在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_AS may be used as well).

因此，唯一的解决方案似乎是将排序规则更改为SQL排序规则(例如，也可以使用SQL_Latin1_General_CP1_CI_AS)。

^* I removed my previous answer as unnecessary

我把以前的回答删掉了，认为没有必要

#2

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:

看起来它也依赖于排序:

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

#3

A VARBINARY cast should work with any collation

VARBINARY cast应该使用任何排序。

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

#4

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"

#5

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 aaa for 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.

编辑:以上是错误的-只是结果网格对待他们的方式。它们以文本模式显示。

#6

I was having the same issue and using nullif solved it for me.

我遇到了同样的问题，用nullif来解决它。

Select nullif(field_with_nullchar,'') from FOO where BAR = 20

#1