So, I know all about the Replace function and a bug with char(0).

I have a column (NVARCHAR(128)) that has some NCHAR(0x0000) characters from a bad import.

I'm using SQL Server 2008 R2.

The collation for the column is: SQL_Latin1_General_CP1_CI_AS.

I have tried all the stuff online that I can possibly find, and NOTHING will get the stinking char(0) characters out of the column.

Here's my latest attempt, with BAFFLING (bug in sql server?) results.

I have a function that loops through each char and replaces 0x0000 with a specific char.

ALTER FUNCTION dbo.ReplaceCharZero
  @testString NVARCHAR(MAX),
  @charToReplaceWith NCHAR(1) = ' '
        @i INT = 1 ,
        @fixedString NVARCHAR(MAX) = ''
    WHILE @i <= LEN(@testString)
            IF SUBSTRING(@testString, @i, 1) = CHAR(0x00)
                    --PRINT 'Found' + CAST(@i AS VARCHAR)
                    SET @fixedString = @fixedString + @charToReplaceWith         
                    --PRINT 'NOT Found' + CAST(@i AS VARCHAR)
                    SET @fixedString = @fixedString
                        + SUBSTRING(@testString, @i, 1)
            SET @i = @i + 1
    RETURN @fixedString               

And here's what I do to test:


    DECLARE @ShortDescription NVARCHAR(128), @SupplierId INT, @Language CHAR(2)

    SELECT TOP 1 @ShortDescription = ShortDescription,
                 @SupplierId = SupplierID,
                 @Language = Language
    FROM   Supplier_Multilingual
    WHERE  ShortDescription LIKE '%' +  CHAR(0x00) + '%'

    SET @ShortDescription =  REPLACE(dbo.ReplaceCharZero(@ShortDescription, ' '), '-', ' ')

    UPDATE dbo.Supplier_MultiLingual
    SET    ShortDescription = NULL 
    WHERE  SupplierID = @SupplierId
    AND    Language = @Language

    UPDATE dbo.Supplier_MultiLingual
    SET    ShortDescription = dbo.ReplaceCharZero(@ShortDescription, '') 
    WHERE  SupplierID = @SupplierId
    AND    Language = @Language

    SELECT *
    FROM   Supplier_Multilingual
    WHERE  SupplierId = @SupplierId
    AND    Language = @Language
    AND    ShortDescription LIKE '%' +  CHAR(0x00) + '%'


In my test, I grab the column as a variable, I run my function on it to strip out the 0x0000, then I update the original column with a NULL, then I update it to my fixed variable, and then I run a query to see if 0x0000 chars still exist, which they do.

  • How do you KNOW the null is not being replaced? – Hannah Vernon Nov 6 '14 at 19:29
  • 1
    It turns out i did NOT know. I was trusting the last statement that was looking for '%' + Char(0x00) + '%'. When it was really just finding spaces, and even worse since char(0x00) is undefined for some collations, it will convert the string to '%%' which matches all – ttomsen Nov 7 '14 at 14:31
  • I have a feeling that converting the original string or result of SUBSTRING() to varbinary could help. – Vladimir Baranov Nov 25 '15 at 6:31
  • @VladimirBaranov Converting to VARBINARY is not necessary. Please see my answer for details :-). – Solomon Rutzky Nov 25 '15 at 6:46
  • @ttomsen Not sure if you saw my answer, but you definitely do not need a UDF to remove CHAR(0x00) / NCHAR(0x0000) :-) – Solomon Rutzky Jun 30 '16 at 18:51

CHAR(0) appears to be converted to a space, or CHAR(32).

The following demonstrates the problem:

SELECT @Data = 'this is a test' + CHAR(0) + 'of null';
SET @i = 1;
WHILE @i < LEN(@Data)
    IF SUBSTRING(@Data, @i, 1) = CHAR(0) 
        SET @txt = 'found a null char at position ' + CONVERT(NVARCHAR(255),@i);        
        RAISERROR (@txt, 0, 1) WITH NOWAIT;
    SET @i = @i + 1;

enter image description here

  • 1
    Yes, I found this too. So the checking query at the end that looks to see if there are any more char(0) finds spaces and tells me incorrectly that their are 0x00 chars remaining in the string. I was chasing a goose :( – ttomsen Nov 7 '14 at 14:30
  • 1
    More info.. in some collations it just translates the 0 char to undefined which in my case was leading to false positives. – ttomsen Nov 7 '14 at 14:33
  • more info for folks, my function up there will remove the 0x00 and replace it, but you'll NEVER know because you can't go back and check it. bad sql server. – ttomsen Mar 13 '15 at 18:15
  • @ttomsen and Max: The CHAR(0) is not actually being converted to a space. The "character" within the @Data variable is still 0x0000, but NCHAR(0) can equate to it when using a non-binary collation (i.e. one that does not end in _BIN or _BIN2, though _BIN has been deprecated for a while now). Please see my answer for more details. – Solomon Rutzky Nov 25 '15 at 17:15

A bug in the REPLACE function? Or in SQL Server in general? I'm not so sure about that. The only "issue" here is merely not fully understanding how string comparisons are handled.

Collations define how certain characters will compare against other characters. Sometimes there are rules about certain combinations of characters equating to one or more other characters. And there are rules about characters such as 0x00 (null) and 0x20 (space) equating to each other or other characters. And, to make life more interesting, there are a few nuances specific to VARCHAR data using a SQL Server Collation (i.e. one starting with SQL_), as the following example shows:

SELECT REPLACE('VARCHAR with SQL_Latin1_General_CP1_CI_AS'+CHAR(0)+'Matches', CHAR(0),
               ': ' COLLATE SQL_Latin1_General_CP1_CI_AS);
SELECT REPLACE(N'NVARCHAR with SQL_Latin1_General_CP1_CI_AS'+NCHAR(0)+N'Matches', NCHAR(0),
               N': ' COLLATE SQL_Latin1_General_CP1_CI_AS);
SELECT REPLACE('VARCHAR with Latin1_General_100_CI_AS'+CHAR(0)+'Matches', CHAR(0),
               ': ' COLLATE Latin1_General_100_CI_AS);
SELECT REPLACE(N'NVARCHAR with Latin1_General_100_CI_AS'+NCHAR(0)+N'Matches', NCHAR(0),
               N': ' COLLATE Latin1_General_100_CI_AS);


VARCHAR with SQL_Latin1_General_CP1_CI_AS: Matches

NVARCHAR with SQL_Latin1_General_CP1_CI_AS

VARCHAR with Latin1_General_100_CI_AS

NVARCHAR with Latin1_General_100_CI_AS

So let's take a look at this behavior using queries based on the ones found in @Max's answer. I added the N prefix to the string literals and to the CHAR(0), and I also added an extra NCHAR(0) just to make the next part easier to see. And I added queries to show the actual Code Points being used (to prove that the 0x0000 values are really in there, and a call to REPLACE() to see if that really has a bug in it).

SELECT @Data = N'this is' + NCHAR(0) + N'a test' + NCHAR(0) + N'of null';


this is


this is

The first result shows that the string ends just after the "is" due to (null) termination. The second result shows the underlying codes, and I emphasized the two instances of the 0x0000 character. The third result shows that the REPLACE function doesn't seem to match the 0x0000 character to the NCHAR(0) that is passed in.

But should we be expecting that NCHAR(0) would be matching here? We can effectively disable all of the equivalence rules that are usually applied to string comparisons by forcing a binary collation. We will use a _BIN2 collation since the _BIN collations are deprecated and shouldn't be used unless you have a specific need for them.

Add the following query to the set above and re-run the batch.

SELECT REPLACE(@Data, NCHAR(0) COLLATE Latin1_General_100_BIN2, N'~');

You should get the following additional result:

this is~a test~of null

So the REPLACE function does in fact work, and this was tested on both SQL Server 2008 R2, SP3 as well as SQL Server 2012 SP2.

Ok, so that only addressed the issue of REPLACE not working with NCHAR(0), but did not address NCHAR(0) equating to a space (i.e. NCHAR(32) or NCHAR(0x20)).

Now we will use an adaptation of the main query from @Max's answer. I again added an extra NCHAR(0) to the test string (really just replaced the space at position 8 with it), and I added the Code Point of the matching character to the RAISERROR message.

SELECT @Data = N'this is' + NCHAR(0) + N'a test' + NCHAR(0) + N'of null';

        @CodePoint INT;
SET @i = 1;

WHILE @i < LEN(@Data)
    IF SUBSTRING(@Data, @i, 1) = NCHAR(0) --COLLATE Latin1_General_100_BIN2
        SET @CodePoint = UNICODE(SUBSTRING(@Data, @i, 1));
        RAISERROR (N'Found a NULL char (Code Point = %d) at position: %d',
                   10, 1, @CodePoint, @i) WITH NOWAIT;
    SET @i = @i + 1;

This query (with the COLLATE clause still commented out) will return:

Found a NULL char (Code Point = 32) at position: 5
Found a NULL char (Code Point = 0) at position: 8
Found a NULL char (Code Point = 32) at position: 10
Found a NULL char (Code Point = 0) at position: 15
Found a NULL char (Code Point = 32) at position: 18

These are the same positions reported in @Max's test, but now it shows what Code Point it is matching in each case. And yes, it is equating to both 32 and 0.

Now, uncomment the COLLATE clause and re-run it. It will return:

Found a NULL char (Code Point = 0) at position: 8
Found a NULL char (Code Point = 0) at position: 15

Another way to accomplish this, and without using the COLLATE clause, is to change the IF statement to be:

IF ( UNICODE(SUBSTRING(@Data, @i, 1)) = 0 )

Of course, neither of these two fixes -- the WHILE loop using either the COLLATE clause or the UNICODE() function -- are needed to address the original problem of removing the 0x0000 characters from the input data since the simple REPLACE (using the COLLATE clause) handles that.


  • If you are wanting to replace / remove characters from a string, there is no need to use a loop. The REPLACE function works just fine as long as you specify a _BIN2 collation for at least one of the 3 input parameters via the COLLATE keyword (and technically it doesn't matter which binary collation since binary collations only compare the numeric Code Point values).
  • If needing to test for a particular Code Point (such as in a loop as shown above), then it is probably fastest to use the UNICODE() function since that just reports what values are actually there. This should be faster than using the COLLATE keyword since that has to do more work.
  • If needing to test for a series of Code Points / characters, then use the COLLATE keyword, specifying a _BIN2 binary collation.
  • 1
    +1. A very nice detailed answer. I didn't think about setting/changing collation, I thought about IF CONVERT(varbinary(2), SUBSTRING(@Data, @i, 1), 0) = convert(varbinary(2), CHAR(0), 0). Your variant with collation looks better. – Vladimir Baranov Nov 25 '15 at 6:51
  • @VladimirBaranov Thanks. I just added a "summary" section to help navigate these types of scenarios. Regarding your thought, you don't need the right side of that equation since it is effectively a constant of 0x0000. But you should use VARBINARY(4) since Supplementary Characters are a pair of 2-byte codes (it won't help for 00 but is a good habit to get into). So you would just do IF ( CONVERT(VARBINARY(4), SUBSTRING(@Data, @i, 1)) = 0x0000 ). But then that's no different than what I showed using the UNICODE function, except for getting a hex / BINARY value instead of a decimal / INT. – Solomon Rutzky Nov 25 '15 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.