blocks|key|3044487|text|看起来C风格的终止符也是SQL中的终止符：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3044488|SELECT++REPLACE(bad,+CHAR(0),+'+')
FROM++++(
+++++++++SELECT+'a'+%2B+CHAR(0)+%2B+'b'+AS+bad
++++++++)+AS+X|code-block|syntax|javascript|3044489|看起来它也依赖于排序规则：|3044490|SELECT++REPLACE(CAST(bad+COLLATE+SQL_Latin1_General_CP1_CI_AS+AS+varchar(10)),+CHAR(0),+'+')
FROM++++(
+++++++++SELECT+'a'+%2B+CHAR(0)+%2B+'b'+AS+bad
++++++++)+AS+X|3044491|与预期相比，工作正常：|3044492|SELECT++REPLACE(CAST(bad+COLLATE+Latin1_General_CI_AS+AS+varchar(10)),+CHAR(0),+'+')
FROM++++(
+++++++++SELECT+'a'+%2B+CHAR(0)+%2B+'b'+AS+bad
++++++++)+AS+X|3044493|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Looks like the C-style terminator is a terminator in SQL as well:

<pre><code>SELECT REPLACE(bad, CHAR(0), ' ')
FROM (
 SELECT 'a' + CHAR(0) + 'b' AS bad
 ) AS X
</code></pre>

Looks like it's also dependent on COLLATION:

<pre><code>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
</code></pre>

works as expected, compared to:

<pre><code>SELECT REPLACE(CAST(bad COLLATE Latin1_General_CI_AS AS varchar(10)), CHAR(0), ' ')
FROM (
 SELECT 'a' + CHAR(0) + 'b' AS bad
 ) AS X
</code></pre>

blocks|key|3258448|text|您确定它们是空字符吗？你是怎么把它们弄进去的？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3258449|看起来SQL+Server将它们视为字符串终止符。此查询：|3258450|select+'aaa'+%2B+char(0)+%2B+'bbb'|code-block|syntax|javascript|3258451|为我返回aaa+(在SQL+Server2008上)。|offset|length|style|CODE|3258452|编辑:上面的是错误的-只是结果网格这样对待它们。它们以文本模式显示。|BOLD|3258453|entityMap^0|0|0|0|4|3|0|0|6|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|V|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|W|8|@$K|X|L|Y|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|Z|8|@$K|10|L|11|M|Q]]|9|@]|A|$]]|$1|R|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|S|$]]

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:

<pre><code>select 'aaa' + char(0) + 'bbb'
</code></pre>

Returns <code>aaa</code> for me (on SQL Server 2008).

Edit: Above is wrong - it's just the results grid that treats them that way. They show up in text mode.

blocks|key|310845|text|我刚刚在我的服务器(2008)上运行了下面的测试，它是成功的。这可能与ANSI设置有关。我将尝试在此处翻转一些设置，看看是否可以重现您的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|310846|DECLARE+@test_null_char+VARCHAR(20)

SET+@test_null_char+=+'aaa'+%2B+CHAR(0)+%2B+'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"|code-block|syntax|javascript|310847|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|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.

<pre><code>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"
</code></pre>

blocks|key|308798|text|VARBINARY强制转换应适用于任何排序规则|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|308799|SELECT+
+++REPLACE(CAST(CAST(fld+AS+VARCHAR(5))+AS+VARBINARY(5)),+0x0,+',')
FROM+
+++(SELECT+'QQ'+%2B+CHAR(0)+%2B+'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'+%2B+CHAR(0)+%2B+'WW'+COLLATE+SQL_Latin1_General_CP1_CI_AS+AS+fld)+AS+T+

>>QQ,WW
>>QQ,WW|code-block|syntax|javascript|308800|entityMap^0|0|9|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

A <code>VARBINARY</code> cast should work with any collation

<pre><code>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 

&gt;&gt;QQ,WW
&gt;&gt;QQ,WW
</code></pre>

blocks|key|308802|text|我也遇到了同样的问题，使用nullif解决了这个问题。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|308803|Select+nullif(field_with_nullchar,'')+from+FOO+where+BAR+=+20|code-block|syntax|javascript|308804|entityMap^0|D|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

I was having the same issue and using <code>nullif</code> solved it for me.

<pre><code>Select nullif(field_with_nullchar,'') from FOO where BAR = 20
</code></pre>

I am wondering what the literal for a <a href="http://en.wikipedia.org/wiki/Null_character" rel="noreferrer">Null character</a> (e.g. '\0') is in TSQL. 

Note: not a NULL field value, but the null character (<a href="http://en.wikipedia.org/wiki/Null_character" rel="noreferrer">see link</a>). 

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: 

<pre><code>select REPLACE(field_with_nullchar, char(0), ',') from FOO where BAR = 20
</code></pre>

What is the Null Character literal in TSQL?

SQL Server

服务器

我想知道在TSQL中的字面量是什么(例如'\0')。注意：不是空字段值，而是空字符()。我有一个混合了典型字符和空字符的列。我正在尝试用不同的值替换空字符。我本以为下面的方法会行得通，但没有成功：select REPLACE(field_with_nullchar, char(0), ',') from FOO whe...

问什么是TSQL中的Null字符文字？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问什么是TSQL中的Null字符文字？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问什么是TSQL中的Null字符文字？
EN