Replace null character in a string in sql

I need to replace a null character in a sql string, i cant seem to find the right command to achieve this. I have used replace (myString ,'\0', '') but this seems not to work, any开发者_运维百科 help would be great

The trick that works is to COLLATE your value to Latin1_General_BIN before using REPLACE and also use nchar(0x00) COLLATE Latin1_General_BIN for string_pattern.

REPLACE ( string_expression , string_pattern , string_replacement )

 select 
 [Terminated]      =          N'123' + nchar(0) + N'567'                                
,[Replaced with -] = REPLACE((N'123' + nchar(0) + N'567') COLLATE Latin1_General_BIN
                                          , nchar(0x00) COLLATE Latin1_General_BIN 
                                                 ,'-')      
,[Removed]        = REPLACE((N'123' + nchar(0) + N'567') COLLATE Latin1_General_BIN
                                    , nchar(0x00)      COLLATE Latin1_General_BIN
                                            ,'')    

Here is the result (use Output To Text):

Contains   Replaced with -   Removed
---------- ----------------- --------
123 567    123-567           123567

Use this:

REPLACE(myString, char(0), '')

These functions remove null characters from Unicode strings, at least in SQL Server 2008.

-- Remove all null characters
CREATE FUNCTION RemoveNulls(@s nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @r nvarchar(max);
    SET @r = REPLACE(@s COLLATE Latin1_General_BIN, NCHAR(0), N'');
    RETURN @r; 
END

-- Remove all characters from the first null character
CREATE FUNCTION TrimNull(@s nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @r nvarchar(max);
    DECLARE @i int = CHARINDEX(NCHAR(0), @s COLLATE Latin1_General_BIN);
    IF @i = 0
        SET @r = @s;
    ELSE
        SET @r = SUBSTRING(@s, 1, @i - 1);
    RETURN @r; 
END

-- Example usage
DECLARE @s nvarchar(10) = N'Test' + NCHAR(0) + N'!';
SELECT dbo.RemoveNulls(@s), dbo.TrimNull(@s);
--> Test!, Test

In my case, fields from ODBC were padded to 8000 characters with null and TrimNull was much faster than RemoveNulls.

For latin characters: select REPLACE('Ho'+CHAR(0)+'mer' COLLATE SQL_Latin1_General_CP1_CS_AS, CHAR(0), '')

For russian characters: select REPLACE(('Го'+CHAR(0)+'мер') COLLATE Cyrillic_General_BIN , CHAR(0), '')

If you Only have ASCII (Char/VarChar) strings then this will work as @DyingCactus suggests:

REPLACE(myString, Char(0x00), '')

However, if you are dealing with Null-Terminated Strings and are trying to fix or convert to something like XML, and your data is Unicode (nChar/nVarChar), then use this:

(CASE WHEN UNICODE(SUBSTRING(myString, LEN(myString), 1)) = 0x0000
      THEN SUBSTRING(myString, 1, LEN(myString) - 1)
      ELSE myString END)

This works for both ASCII (Char/VarChar) and Unicode (nChar/nVarChar).

Note

Using the Replace() function with Char(0) or nChar(0) will NOT work for Unicode (nChar/nVarChar).
It's a bug in the SQL Server Replace() function.

You could cast as VarChar, then use Replace(), but then you would lose any special Unicode/Non-ASCII characters you might have intended to keep.
Otherwise you wouldn't have used the Unicode datatype (that takes up twice as much space to store your data) in the first place.

If you have Null-Characters mixed in with your Unicode strings (and not only at the end), and, for the purposes of your query, maintaining Unicode-specific characters are unimportant, then as a last resort you could use this :

(CASE WHEN myString LIKE (N'%' + nCHAR(0x0000) + N'%')--Has Null-Character(s).
      THEN REPLACE(CAST(myString as VarChar(MAX)), Char(0x00), '')--Cast as ASCII
      ELSE myString END)--Else, leave as Unicode to preserve Unicode-Only chars.

I'm not completely sure what is wrong with your strings, but here are some things to try, are you using varchar?, edit question with more details:

if you have NULL characters within a string:

declare @x varchar(10)
set @x='123'+char(0)+'456'
SELECT @x AS Has_NULL_in_it, REPLACE(@x, char(0), '') AS Has_NULL_removed

OUTPUT:

Has_NULL_in_it Has_NULL_removed
-------------- ----------------
123 456        123456

(1 row(s) affected)

If you can't tell the character within the string, try this ASCII:

DECLARE @y varchar(10),@c int
set @y='123'+char(0)+'456'
set @c=0
WHILE @c<LEN(@y)
BEGIN
    SET @c=@c+1
    PRINT CONVERT(varchar(5),@c)+' - '+SUBSTRING(@y,@c,1)+' - CHAR('+CONVERT(varchar(5),ASCII(SUBSTRING(@y,@c,1)))+')'
END

OUTPUT:

1 - 1 - CHAR(49)
2 - 2 - CHAR(50)
3 - 3 - CHAR(51)
4 - - CHAR(0)
5 - 4 - CHAR(52)
6 - 5 - CHAR(53)
7 - 6 - CHAR(54)

try this unicode:

DECLARE @y nvarchar(10),@c int
set @y='123'+char(0)+'456'
set @c=0
WHILE @c<LEN(@y)
BEGIN
    SET @c=@c+1
    PRINT CONVERT(nvarchar(5),@c)+' - '+SUBSTRING(@y,@c,1)+' - UNICODE('+CONVERT(nvarchar(5),UNICODE(SUBSTRING(@y,@c,1)))+')'
END

if your have strings that are completely NULL:

declare @z varchar(10)
set @z=NULL
select @z AS IS_NULL, ISNULL(@Z,'') AS NULL_Removed

OUTPUT:

IS_NULL    NULL_Removed
---------- ------------
NULL       

(1 row(s) affected)

If you are concatenating values to get your string use IsNull(value, replacement) to avoid having null values or set CONCAT_NULL_YIELDS_NULL ON to avoid null strings as a result.

We had the same problem: Ending \0 character in nvarchar fields and unable to replace it with any of the REPLACE variants proposed (SQL Server 2008). When using

LEFT(Bar, LEN(Bar)-1)

it cut off the last regular character together with the \0 !

Our solution now to correct the fields is (as weird as it may seem on a first glimpse):

UPDATE Foo
    SET Bar = LEFT(Bar, LEN(Bar))
WHERE RIGHT(Bar, 1) = CHAR(0)

Examples resolved

CREATE FUNCTION dbo.F_ReplaceNullChar( @STR NVARCHAR(MAX) )
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @i INT=0
DECLARE @RET NVARCHAR(MAX)=''
    WHILE @I<LEN(@STR) 
    BEGIN 
        SET @i=@i+1
        IF UNICODE(SUBSTRING(@STR,@i,1)) <> 0x0000
            SET @RET=@RET+SUBSTRING(@STR,@i,1)

    END
    RETURN @RET
END
GO

SELECT LEN(mycol) lenbefore,mycol,
 LEN( dbo.F_ReplaceNullChar(mycol)) lenafter, dbo.F_ReplaceNullChar(mycol) mycolafter 
FROM mytab

select zz.xx , replace(zz.xx, '�', '') from ( select t.string_with_null, ( select s.string_with_null+'' from TABLE_1 s where s.token_hash = t.token_hash for xml path('') ) xx from TABLE_1 t(nolock) )zz