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

• Ant-Green

  SSC Guru

  Points: 113542

  More actions

  July 14, 2014 at 4:58 am

  #1729096

  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: 182947

  More actions

  July 14, 2014 at 5:06 am

  #1729099

  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

  July 14, 2014 at 5:06 am

  #1729100

  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

  July 14, 2014 at 5:11 am

  #1729103

  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