RTRIM not removing trailing spaces

Question

RTRIM not removing trailing spaces

Ian C0ckcroft

Hall of Fame

Points: 3373
More actions
July 14, 2014 at 4:42 am

#296441

Hi guys, I am loading a dimension using a distinct query.
There are duplicates coming through and the only differnce is a trailing space on one of the columns.
RTRIM is not removing the space.
any ideas why? and how i can fix it?
thanks guys
Ian Cockcroft
MCITP BI Specialist

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

I would first check to see if it is actually a trailing space that is at the end of the string, as it might be a hidden character and therefore RTRIM wont remove it.

declare @string varchar(max) = 'abc ®', @char char(1), @pos int, @len int

select @pos = 1, @len = len(@string)

while @pos <= @len

begin

select @char = substring(@string,@pos,1)

print @char + ' - ' + convert(varchar,ascii(@char))

set @pos = @pos + 1

end

Then you can target the removal with a replace on the char(##) of the ascii character if it isn't a trailing space.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

Quick question, what do you get from the following query?

😎

SELECT UNICODE(RIGHT([the string in question here],1))

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 3

Ian C0ckcroft (7/14/2014)
Hi guys, I am loading a dimension using a distinct query.
There are duplicates coming through and the only differnce is a trailing space on one of the columns.
RTRIM is not removing the space.
any ideas why? and how i can fix it?
thanks guys

I have seen that when string have special character, RTRIM doesnt remove the space. When I faced the issue, I searched the special character, removed it then RTRIM worked.

Thanks

Ian C0ckcroft Hall of Fame Points: 3373 More actions · Answer 4

Thanks guys, I copied the result and put it into text pad so i could see the formating. It shows a space and thats why I assumed as much. will follow the special character angle.

thanks a mil

Ian Cockcroft
MCITP BI Specialist