Replace null character in a string in sql

Question

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

Answer 1

Use this:

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

Answer 2

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.

Answer 3

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)

Answer 4

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), '')