Sql Server - Remove End String Character "\0" From Data

Question

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

Is there a sql query I can write to easily remove this character from all the records that are affected? I can get all the affected records, but I don't have a way to update the record to a new value (without the "\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

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

Edit: Just to redeem this answer! Might be useful for the more general case that a string has embedded \0s.

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 2

Does...

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

...work?

SUBSTRING(naughtyfield, 1, LEN(naughtyfield) - 1) on those fields that are null-terminated works - but be careful not to use it on non-NULL terminated strings or you'll be losing data.