Sql-server – Cannot remove char 0x0000 from NVARCHAR column

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) = ' '
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE
        @i INT = 1 ,
        @fixedString NVARCHAR(MAX) = ''
    WHILE @i <= LEN(@testString)
        BEGIN
            IF SUBSTRING(@testString, @i, 1) = CHAR(0x00)
                BEGIN
                    --PRINT 'Found' + CAST(@i AS VARCHAR)
                    SET @fixedString = @fixedString + @charToReplaceWith         
                END
            ELSE
                BEGIN
                    --PRINT 'NOT Found' + CAST(@i AS VARCHAR)
                    SET @fixedString = @fixedString
                        + SUBSTRING(@testString, @i, 1)
                END
            SET @i = @i + 1
        END
    RETURN @fixedString               
END

And here's what I do to test:

BEGIN TRAN

    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) + '%'

ROLLBACK TRAN

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.