7

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
Improve this question
3
  • So, how do I do this? Will's answer is not correct, but his help led me to my solution. Commented Aug 20, 2010 at 18:03
  • If Will moves his comment about using SUBSTRING(naughtyfield, 1, LEN(naughtyfield) - 1) into his answer that should solve the problem? Commented Aug 20, 2010 at 18:06
  • Yeah ... though your answer is more elegant, I don't think I can create a function on the Prod database just for this "quick fix". Too much paperwork. Commented Aug 20, 2010 at 18:08

3 Answers 3

Reset to default
11

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

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.

Improve this answer

11 Comments

No. From my quick test. create table #t (col varchar(50)) insert into #t values ('hgjh' + char(0) + 'gghg' ) UPDATE #t SET col = REPLACE(col,char(0),'')
Does LEN(naughtyfield) return the length including the null-terminator in MSSQL?
Yes, Len('The string in the column') = X, Len(naughtyfield) = X + 1
Use SUBSTRING(naughtyfield, 1, LEN(naughtyfield) - 1) on those fields that are null-terminated - that works.
I am not sure ALL the fields will have it, so I might be cutting off good data
|
2

I faced this problem and fixed it recently. It seems it's always at the end of a string (as it's a terminator character).

Also, because it's not a white space, it causes rtrim not to work on fields that have extra trailing spaces (e.g. 'California             \0').

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

Here is how I safely removed it in my case

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

And here is removing it and also trimming the extra spaces.

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.