RTRIM not working

Question

Post reply

RTRIM not working

Kingfish

Mr or Mrs. 500

Points: 516
More actions
August 8, 2008 at 11:26 am

#75278

I have a varchar field named email in which I cannot remove trailing spaces (char(32)). Neither RTRIM or REPLACE functions work. The LEN and DATALENGTH functions return 42 however there are numerous spaces after the 42nd character. If I convert the field to nvarchar the DATALENGTH function returns 84. When I use the ASCII function it identifies the trailing characters as 32. Can anyone help with his issue?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I've never had a problem with rtrim. How do you know that it isn't working?

😎

Kingfish Mr or Mrs. 500 Points: 516 More actions · Answer 2

I use the ASCII function to return the ascii characters of the string and it returns char(32) numerous times on the end of the string. When I run a REPLACE on the field the script below still returns the same rows. Below is the script I use.

declare @position int, @string char(128)

set @position = 1

select @string = email from sysdba.contact where contactid = 'C6UJ9A02RNDG'

while @position <= datalength(@string)

begin

select ascii(substring(@string, @position, 1)),

char(ascii(substring(@string, @position, 1)))

set @position = @position + 1

end

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Alan Frelich (8/8/2008)
I use the ASCII function to return the ascii characters of the string and it returns char(32) numerous times on the end of the string. When I run a REPLACE on the field the script below still returns the same rows. Below is the script I use.
declare @position int, @string char(128)
set @position = 1
select @string = email from sysdba.contact where contactid = 'C6UJ9A02RNDG'
while @position <= datalength(@string)
begin
select ascii(substring(@string, @position, 1)),
char(ascii(substring(@string, @position, 1)))
set @position = @position + 1
end

I see the problem, @string is defined as char(128). This will pad the string with spaces.

Edit:

What are you try to accomplish here?

😎

Kingfish Mr or Mrs. 500 Points: 516 More actions · Answer 4

That was it. Stupid mistake on my part. Thank you for the help.:satisfied: