Rtrim not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Rtrim not working

Author

Topic

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-06-10 : 07:33:58

Hi,

I've some data in one table in which some records are having a space appended to one of the field's value.
I tried rtrim for updating those values but it didn't worked.

This column is defined as varchar
Sample_value : 0-400033

i tried fixing it by using:

Update table set columnvalue= ltrim(rtrim(sample_value))
where pk= @pk

But this didn't worked for me..

Can anyone help?

Thanks,
Sourav

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 07:41:27

Maybe the extra space isn't ordinary spaces (ASCII 32, soft space)?
Maybe they are "hard space", ASCII 160?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 07:42:24

Update table
set columnvalue = ltrim(rtrim(replace(sample_value, char(160), char(32))))
where pk = @pk

E 12°55'05.63"
N 56°04'39.26"

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-06-10 : 07:42:30

if the column is varchar what is the reason for trimming it in the first place, with a varchar it will only use the amount of characters used, rtrim would really be used if a column was declared as eg. char(30) and the column data is only ever 10 characters long

companionz
Yak Posting Veteran

54 Posts

Posted - 2009-06-10 : 07:54:02

Peso,

Thanks for instant reply.. but even this is not working..

@Neil,

the value that is stored in the db is with extra space and when fetching it gives discrepancies..

Thanks,
Sourav

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 07:58:24

Are you sure the extra space is ASCII 32?
Any chance it's ASCII 13 or ASCII 10?

Update table
set columnvalue = ltrim(rtrim(replace(sample_value, char(160), char(32))))
where pk = @pk

Update table
set columnvalue = ltrim(rtrim(replace(sample_value, char(13), char(32))))
where pk = @pk

Update table
set columnvalue = ltrim(rtrim(replace(sample_value, char(10), char(32))))
where pk = @pk

E 12°55'05.63"
N 56°04'39.26"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-10 : 12:52:01

You could try something like this to see what data is really in that column:

DECLARE @Val VARCHAR(50)
DECLARE @i INT

SET @Val = 'foo   '
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

bjarkimg
Starting Member

1 Post

Posted - 2009-07-23 : 10:34:19

Had the same problem.
it was char(10) and char(13) that rtrim and ltrim did not remove.

Thanks for the tips.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources