sql server 2005 - 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 ,'', '') but this seems not to work, any help would be great

Answer 1

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