SQL Server 2008 : how to remove char(0)

Question

I have a column that seems to have a space at the end. I tried using ltrim and rtrim and it doesn't work.

I tried running this query:

DECLARE @Val NVARCHAR(250)
DECLARE @i INT

SET @Val = (select companyname from companies where companyid=3839)
SET @i = 0

WHILE @i <= DATALENGTH(@Val)
BEGIN
    PRINT SUBSTRING(@Val, @i, 1) + ' : ' + CAST(ASCII(SUBSTRING(@Val, @i, 1)) AS VARCHAR(3))
    SET @i = @i + 1
END

and I see the last 2 rows are 32 and 0 = char(32) and char(0).

I tried to use replace:

select replace(replace(companyname, char(0),''), char(32), '')  
from companies 
where companyid = 3839

But it also does not work.

Any other solutions?

Column type: nvarchar(250)
Column collation: Hebrew_CI_AS
SQL Server version: Microsoft SQL Server 2008 R2 (RTM)
10.50.1600.1 (X64) Express Edition with Advanced Services (64-bit)
on Windows NT 6.1 (Build 7601: Service Pack 1)

Also tried this and I still see a space at the end:

Set @StringToClean = Replace(@StringToClean,CHAR(0),CHAR(32));
    --Horizontal Tab
    Set @StringToClean = Replace(@StringToClean,CHAR(9),CHAR(32));
    --Line Feed
    Set @StringToClean = Replace(@StringToClean,CHAR(10),CHAR(32));
    --Vertical Tab
    Set @StringToClean = Replace(@StringToClean,CHAR(11),CHAR(32));
    --Form Feed
    Set @StringToClean = Replace(@StringToClean,CHAR(12),CHAR(32));
    --Carriage Return
    Set @StringToClean = Replace(@StringToClean,CHAR(13),CHAR(32));
    --Column Break
    Set @StringToClean = Replace(@StringToClean,CHAR(14),CHAR(32));
    --Non-breaking space
    Set @StringToClean = Replace(@StringToClean,CHAR(160),CHAR(32));

    Set @StringToClean = LTRIM(RTRIM(@StringToClean));

To physically remove data you need to update the field not just select it. — HLGEM, Sep 09 '14 at 15:06
what do you mean? It was a sample only. I mean that after that Im running 'select @StringToClean' and still see the SPACE — Shoham, Sep 09 '14 at 15:28
you might look at this questions: http://stackoverflow.com/questions/10952252/in-sql-server-replace-a-char0-the-null-character-embedded-in-a-string-with — HLGEM, Sep 09 '14 at 15:41
@HLGEM - thanks but it also didn't work. I gave up and did it like this: update companies set companyname = left(companyname,len(companyname)-2) where companyname like '% ' — Shoham, Sep 10 '14 at 05:40

score 1 · Accepted Answer · answered Nov 25 '14 at 11:03

I got this issue again and I found a nice solution online:

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

and the do this:

update tbl set col = RemoveNullChars(col)

score 0 · Answer 2 · answered Sep 09 '14 at 15:10

0

do not mix nvarchar and varchar or nchar and char, you could get a situation where encoding conversion issue will hit you.

your saying that the column is nvarchar

use nvarchar / nchar

answered Sep 09 '14 at 15:10

Fredou

19,848
10
58
113

just edited the post. its the same even when Im using NVARCHAR(250) – Shoham Sep 09 '14 at 15:13

SQL Server 2008 : how to remove char(0)

2 Answers2