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
– HLGEM, Commented Sep 9, 2014 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
– Shoham, Commented Sep 9, 2014 at 15:28
you might look at this questions: stackoverflow.com/questions/10952252/… — HLGEM
– HLGEM, Commented Sep 9, 2014 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
– Shoham, Commented Sep 10, 2014 at 5:40

Shoham · Accepted Answer · 2014-11-25 11:03:15Z

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)

Fredou · Accepted Answer · 2014-09-09 15:10:26Z

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

just edited the post. its the same even when Im using NVARCHAR(250)

Collectives™ on Stack Overflow

SQL Server 2008 : how to remove char(0)

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related