Sql Server - remove the end of line character "\ 0" from data

Question

I have a column in a database (SQL Server 2005) that has data with "\ 0" at the end. When querying in SQL Server, this character is not displayed and does not seem to exist. When I look at my C # code, there is a symbol there. This symbol causes an error on our website, and we need it to be removed from all affected lines.

Is there an SQL query that I can write to easily remove this character from all affected records? I can get all the affected records, but I have no way to update the record to a new value (without "\ 0").

UPDATE: This seems to work:

Select * from TABLE where UNICODE(SUBSTRING(naughtyField, LEN(naughtyField), 1)) = 0 

So:

 Update TABLE SET naughtyField = SUBSTRING(naughtyField, 1, LEN(naughtyField) - 1) where UNICODE(SUBSTRING(naughtyField, LEN(naughtyField), 1)) = 0 

Answer 1

Whether there is a...

 UPDATE mytable SET myfield = REPLACE(myfield, CHAR(0), '') 

... work?

SUBSTRING(naughtyfield, 1, LEN(naughtyfield) - 1) in those fields that work with null termination, but be careful not to use it for non-null strings, or you will lose data.

Answer 2

UPDATE tbl SET col = REPLACE (col, char (0), '')

Edit: just to atone for this answer! May be useful for a more general case, when a string has inline \0 s.

 CREATE FUNCTION dbo.RemoveNullChars ( @string NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) WITH RETURNS NULL ON NULL INPUT AS BEGIN DECLARE @Result NVARCHAR(MAX) SET @Result = '' DECLARE @counter INT SET @counter = 0 WHILE (@counter <= LEN(@string)) BEGIN IF UNICODE(SUBSTRING(@string,@counter,1)) <> 0 SET @Result = @Result + SUBSTRING(@string,@counter,1) SET @counter = @counter + 1 END RETURN @Result END 

Then

  UPDATE tbl SET col = dbo.RemoveNullChars (col) 

Answer 3

I ran into this problem and fixed it recently. It seems to be always at the end of the line (as a terminator character).

Also, since this is not empty space, it causes rtrim to not work with fields that have extra trailing spaces (for example, "California" and "nbsp;;. \ 0 ')

The safest way to remove this is a substring from (0) to (the last index is "\ 0" or the length of the string if "\ 0" is not found)

This is how I safely deleted it in my case

  substring([field], 0, ( LEN([field]) - CHARINDEX(char(0), REVERSE([field])) + 1 ) ) 

And here we delete it, and also cut off the extra spaces.

  ltrim(rtrim(substring([field], 0, ( LEN([field]) - CHARINDEX(char(0), REVERSE([field])) + 1 ) ) ))