Replace null character in a string in sql

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 

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