What is the Null Character literal in TSQL?

Question

I am wondering what the literal for a Null character (e.g. '\0') is in TSQL.

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

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

This behaviour was submitted to Microsoft nearly 4 years ago:

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.

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

^* I removed my previous answer as unnecessary

Answer 2

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

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

Answer 3

A VARBINARY cast should work with any collation

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

Answer 4

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

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

Answer 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:

select 'aaa' + char(0) + 'bbb'

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

Answer 6

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.

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"