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
Martin
  • 11,031
  • 8
  • 50
  • 77
  • So, how do I do this? Will's answer is not correct, but his help led me to my solution. – Martin Aug 20 '10 at 18:03
  • If Will moves his comment about using `SUBSTRING(naughtyfield, 1, LEN(naughtyfield) - 1)` into his answer that should solve the problem? – Martin Smith Aug 20 '10 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. – Martin Aug 20 '10 at 18:08

3 Answers3

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)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
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.

Will A
  • 24,780
  • 5
  • 50
  • 61
  • 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),'')` – Martin Smith Aug 20 '10 at 17:33
  • Does LEN(naughtyfield) return the length including the null-terminator in MSSQL? – Will A Aug 20 '10 at 17:36
  • Yes, Len('The string in the column') = X, Len(naughtyfield) = X + 1 – Martin Aug 20 '10 at 17:37
  • 1
    Use `SUBSTRING(naughtyfield, 1, LEN(naughtyfield) - 1)` on those fields that are null-terminated - that works. – Will A Aug 20 '10 at 17:38
  • I am not sure ALL the fields will have it, so I might be cutting off good data – Martin Aug 20 '10 at 17:40
  • "I can get all the affected records" - can you? – Will A Aug 20 '10 at 17:41
  • Well, yeah, but I would like a cleaner solution, one that gives me better confidence I am not removing something I shouldn't be – Martin Aug 20 '10 at 17:42
  • When I cast to VARBINARY, I get 00 on the end of the string. You didn't did you? – Will A Aug 20 '10 at 17:44
  • Take a look @ http://stackoverflow.com/questions/2828333/what-is-the-null-character-literal-in-tsql - there are a few ideas there that could be useful to you - sounds like this is a collation specific issue. – Will A Aug 20 '10 at 17:48
  • @Will (a) Move your substring comment into your answer so he can accept it! (b) I tried some ideas from that question you linked but they didn't seem to work for nvarchar columns `declare @t table(col nvarchar(50)) insert into @t values (N'日' + char(0) + N'国' ) UPDATE @t SET col = REPLACE(cast(col COLLATE SQL_Latin1_General_CP1_CI_AS as nvarchar(max)), CHAR(0), '') select col, CAST(col as varbinary) from @t` – Martin Smith Aug 20 '10 at 18:29
  • Careful! The Replace() function will NOT work with UNICODE (NVarChar, NChar) datatypes. – MikeTeeVee Mar 30 '13 at 00:52
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 ) ) ))
H7O
  • 859
  • 8
  • 5