RTRIM is not working as expected

• Paul Hernández

  SSCarpal Tunnel

  Points: 4961

  More actions

  April 21, 2015 at 6:52 am

  #296150

  Hi friends,

  for some weird reason the RTRIM function is not working as I expected.

  Example:

  SELECT

  DISTINCT

  [dim_vArticles].[ArticleId] AS [dim_vArticlesArticleId0_0],

  rtrim([StyleDescription]) AS [dim_vArticlesStyleDescription0_7]

  , ASCII(RIGHT([StyleDescription],1))

  FROM [dim].[vArticles] AS [dim_vArticles]

  where ArticleId = '82-4056204122396-129'

  Result ( I will put the strings into quotation marks):

  ArticleId= '82-4056204122396-129'

  StyleDesctiption = 'Golf Padded Vest '　

  ASCII = 32

  I have no idea, it should remove the ASCII code 32 but it does not.

  Any help will be appreciated.

  Kind Regards,

  Paul Hernández

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  April 21, 2015 at 7:03 am

  #1792384

  What is the table definition?

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Paul Hernández

  SSCarpal Tunnel

  Points: 4961

  More actions

  April 21, 2015 at 8:21 am

  #1792420

  Hi,

  here a snap-shot from management studio:

  

  Thanks for the quick answer!

  Paul Hernández

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  April 21, 2015 at 8:49 am

  #1792433

  SELECT subQuery.N + '|',

  subQuery.X + '|',

  CASE WHEN CAST(ASCII(SUBSTRING(REVERSE(subQuery.N), 1, 1)) AS VARCHAR(3)) = CAST(ASCII(SUBSTRING(REVERSE(subQuery.X),

  1, 1)) AS VARCHAR(3))

  THEN 'Can''t remove character CHAR('

  + CAST(ASCII(SUBSTRING(REVERSE(subQuery.N), 1, 1)) AS VARCHAR(3))

  + ')'

  ELSE 'Removed character CHAR('

  + CAST(ASCII(SUBSTRING(REVERSE(subQuery.N), 1, 1)) AS VARCHAR(3))

  + ')'

  END

  FROM ( SELECT valuesConstructor.N,

  RTRIM(valuesConstructor.N) AS X

  FROM ( VALUES ( N'TEST' + CHAR(0)), ( N'TEST' + CHAR(1)),

  ( N'TEST' + CHAR(9)), ( N'TEST' + CHAR(10)),

  ( N'TEST' + CHAR(13)), ( N'TEST' + CHAR(28)),

  ( N'TEST' + CHAR(29)), ( N'TEST' + CHAR(30)),

  ( N'TEST' + CHAR(31)), ( N'TEST' + CHAR(32)) ) valuesConstructor ( N )

  ) subQuery;

  I get: -

  Can't remove character CHAR(0)

  Can't remove character CHAR(1)

  Can't remove character CHAR(9)

  Can't remove character CHAR(10)

  Can't remove character CHAR(13)

  Can't remove character CHAR(28)

  Can't remove character CHAR(29)

  Can't remove character CHAR(30)

  Can't remove character CHAR(31)

  Removed character CHAR(32)

  Just to make sure you don't have something else there, can you try: -

  SELECT

  DISTINCT

  [dim_vArticles].[ArticleId] AS [dim_vArticlesArticleId0_0],

  RTRIM([StyleDescription]) AS [dim_vArticlesStyleDescription0_7],

  ASCII(RIGHT(RTRIM([StyleDescription]), 1))

  FROM [dim].[vArticles] AS [dim_vArticles]

  WHERE ArticleId = '82-4056204122396-129';

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Paul Hernández

  SSCarpal Tunnel

  Points: 4961

  More actions

  April 21, 2015 at 8:57 am

  #1792441

  Hi Cadavre,

  your script is the best explanation I have ever seen for the ASCII characters an trimming functions. But that is exactly my problem, that the last position with ASCII Code = 32 is not being removed:

  

  Paul Hernández

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  April 21, 2015 at 9:02 am

  #1792444

  Paul Hernández (4/21/2015)

  ---

  Hi Cadavre,

  your script is the best explanation I have ever seen for the ASCII characters an trimming functions. But that is exactly my problem, that the last position with ASCII Code = 32 is not being removed:

  No problem.

  I guess you missed this bit: -

  Cadavre (4/21/2015)

  ---

  Just to make sure you don't have something else there, can you try: -

  SELECT

  DISTINCT

  [dim_vArticles].[ArticleId] AS [dim_vArticlesArticleId0_0],

  RTRIM([StyleDescription]) AS [dim_vArticlesStyleDescription0_7],

  ASCII(RIGHT(RTRIM([StyleDescription]), 1))

  FROM [dim].[vArticles] AS [dim_vArticles]

  WHERE ArticleId = '82-4056204122396-129';

  I'm wondering if you have an invisible character between the CHAR32 and the last visible character.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Paul Hernández

  SSCarpal Tunnel

  Points: 4961

  More actions

  April 21, 2015 at 9:20 am

  #1792455

  I have no luck today :w00t:

  here the results of your query:

  

  Other interesting results:

  

  

  Thanks for your time!

  Paul Hernández

• TomThomson

  SSC Guru

  Points: 104773

  More actions

  April 21, 2015 at 2:11 pm

  #1792564

  I find it hard to understand why you think it should not return 32.

  You are generating a column called [dim_vArticlesStyleDescription0_7] which is a copy of [StyleDescription] with the trailing spaces removed. Then you are selecting the last character of [StyleDescription] and finding that it's a space. You aren't selecting the last character of [dim_vArticlesStyleDescription0_7] - if you want that you need to write select ascii(right(rtrim([StyleDescription]),1)), since of course the new column name isn't available for use in the select that generates it.

  Tom
  

• Paul Hernández

  SSCarpal Tunnel

  Points: 4961

  More actions

  April 22, 2015 at 2:25 am

  #1792633

  Hi Tom, thanks for your answer.

  Please forget my first post, I copied and pasted the query that SSAS issues, therefore contains this weird aliasing.

  Here is a better code:

  SELECT

  DISTINCT

  [dim_vArticles].[ArticleId],

  '"'+rtrim([StyleDescription])+'"' AS StyleDescription

  , ASCII(RIGHT([StyleDescription],1)) ASCIIChar

  FROM [dim].[vArticles] AS [dim_vArticles]

  where ArticleId = '82-4056204122396-129'

  Here is the result:

  

  I expect the blank space at the end to be removed, but as it can be seen is still there, so the question is, why the RTRIM function does not remove the space at the end of the string?

  Thanks for your help!

  Paul Hernández

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  April 22, 2015 at 2:47 am

  #1792635

  Paul Hernández (4/22/2015)

  ---

  Hi Tom, thanks for your answer.

  Please forget my first post, I copied and pasted the query that SSAS issues, therefore contains this weird aliasing.

  Here is a better code:

  SELECT

  DISTINCT

  [dim_vArticles].[ArticleId],

  '"'+rtrim([StyleDescription])+'"' AS StyleDescription

  , ASCII(RIGHT([StyleDescription],1)) ASCIIChar

  FROM [dim].[vArticles] AS [dim_vArticles]

  where ArticleId = '82-4056204122396-129'

  I expect the blank space at the end to be removed, but as it can be seen is still there, so the question is, why the RTRIM function does not remove the space at the end of the string?

  Thanks for your help!

  What Tom is trying to get at is the same as what I was trying to show.

  Think about your code like this: -

  1. Right trim of the StyleDescription, appears to leave behind a character

  2. Non-right trim of the StyleDescription has CHAR(32) as the far right character

  When you run the ASCII check, you need to run the right trim first. For example: -

  SELECT [ArticleId],

  CHAR(34) + [StyleDescription] + CHAR(34) AS [Not Trimmed],

  CHAR(34) + [TRIMMED] + CHAR(34) AS [Trimmed],

  ASCII(RIGHT([StyleDescription], 1)) AS [Last Character - Not Trimmed],

  ASCII(RIGHT([TRIMMED], 1)) AS [Last Character - Trimmed]

  FROM ( SELECT DISTINCT

  [dim_vArticles].[ArticleId],

  [StyleDescription],

  RTRIM([StyleDescription]) AS [TRIMMED]

  FROM [dim].[vArticles] AS [dim_vArticles]

  WHERE ArticleId = '82-4056204122396-129'

  ) base;

  p.s. Sorry this is a late reply, had a busy evening.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Paul Hernández

  SSCarpal Tunnel

  Points: 4961

  More actions

  April 22, 2015 at 3:48 am

  #1792653

  Thanks so much again you guys for your priceless support:

  @Cadevre: I got the point, I ran your query but this is the result:

  

  I don't know if I am too tire but in my point of view the trimming is not working.

  Paul Hernández

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  April 22, 2015 at 4:48 am

  #1792664

  All I can think is that you have invisible characters. . .:ermm:

  Mind trying to see what all of the character codes of the string are?

  WITH CTE ( N )

  AS ( SELECT 1

  FROM ( VALUES ( 1), ( 1), ( 1), ( 1), ( 1), ( 1), ( 1),

  ( 1), ( 1), ( 1) ) a ( N )

  ),

  CTE2 ( N )

  AS ( SELECT 1

  FROM CTE x

  CROSS JOIN CTE y

  ),

  CTE3 ( N )

  AS ( SELECT TOP ( SELECT LEN([StyleDescription])

  FROM ( SELECT DISTINCT

  [dim_vArticles].[ArticleId],

  [StyleDescription]

  FROM [dim].[vArticles] AS [dim_vArticles]

  WHERE ArticleId = '82-4056204122396-129'

  ) a )

  N

  FROM CTE2

  ),

  TALLY ( N )

  AS ( SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL

  ) )

  FROM CTE3

  )

  SELECT letters AS characterCode,

  ASCII(letters) AS character,

  position

  FROM ( SELECT DISTINCT

  [dim_vArticles].[ArticleId],

  [StyleDescription]

  FROM [dim].[vArticles] AS [dim_vArticles]

  WHERE ArticleId = '82-4056204122396-129'

  ) a

  CROSS APPLY ( SELECT SUBSTRING([StyleDescription], N, 1),

  N

  FROM TALLY

  ) ca ( letters, position );

  I'm not proud of that code in the slightest :sick:

  Other option would be to convert to VARBINARY, then post the string so that I can see if I can reproduce the behaviour here: -

  SELECT DISTINCT

  [dim_vArticles].[ArticleId],

  [StyleDescription],

  CAST([StyleDescription] AS VARBINARY(MAX))

  FROM [dim].[vArticles] AS [dim_vArticles]

  WHERE ArticleId = '82-4056204122396-129';

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  April 22, 2015 at 5:21 am

  #1792678

  I second Cadavre's suggestion please show column dump.

  The script below reproduces what looks like untrimmable blank, but reported ASCII is not 32.

  declare @1 varbinary(100) = cast(N'A' as varbinary(100))

  declare @2 varchar(100) = cast(@1 as varchar(100)) + CHAR(0)+ CHAR(32)

  select @1 = cast(@2 as varbinary(100));

  declare @notrim nvarchar(50) = @1;

  select @1 dump, rtrim(@notrim)+'#' looks_like_not_trimmed_blank

  , ASCII(right(@notrim,1))

• Louis Hillebrand

  SSCarpal Tunnel

  Points: 4982

  More actions

  April 22, 2015 at 5:26 am

  #1792682

  In Unicode there are more Space-Characters than ASCII(32)

  You can use UNICODE(Char) to get the Unicode value for the space-character.

  Run the following code to see the (known to me) space-Characters in Unicode, the ASCII equivalent (sometimes 32) and the way RTRIM handles them

  DECLARE @test-2 TABLE (SpaceChar nvarchar(1), Name nvarchar(50) );

  INSERT @test-2

  VALUES(CHAR(32), 'Space'),

  (NCHAR (0xA0),'No-Break Space' ),

  (NCHAR(0x2002),'En Space'),

  (NCHAR(0x2003),'Em Space'),

  (NCHAR(0x2004),'Three-Per-Em Space'),

  (NCHAR(0x2005),'Four-Per-Em Space'),

  (NCHAR(0x2006),'Six-Per-Em Space'),

  (NCHAR(0x2007),'Figure Space'),

  (NCHAR(0x2008),'Punctuation Space'),

  (NCHAR(0x200A),'Hair Space'),

  (NCHAR(0x200B),'Zero Width Space'),

  (NCHAR(0x202F),'Narrow No-Break Space'),

  (NCHAR(0x205F),'Medium Mathematical Space'),

  (NCHAR(0xFEFF) , 'Zero Width No-Break Space');

  SELECTName,

  ASCII(SpaceChar)'ASCII',

  UNICODE(SpaceChar)'UNICODE',

  CHAR(34) + SpaceChar + char(34)'String' ,

  ASCII(RIGHT(RTRIM('A' + SpaceChar), 1)) 'TRIMMED'

  FROM@test-2

• Louis Hillebrand

  SSCarpal Tunnel

  Points: 4982

  More actions

  April 22, 2015 at 5:35 am

  #1792683

  Changed my code to show the effects better

  DECLARE @test-2 TABLE (SpaceChar nvarchar(1), Name nvarchar(50) );

  INSERT @test-2

  VALUES(CHAR(32), 'Space'),

  (NCHAR (0xA0),'No-Break Space' ),

  (NCHAR(0x2002),'En Space'),

  (NCHAR(0x2003),'Em Space'),

  (NCHAR(0x2004),'Three-Per-Em Space'),

  (NCHAR(0x2005),'Four-Per-Em Space'),

  (NCHAR(0x2006),'Six-Per-Em Space'),

  (NCHAR(0x2007),'Figure Space'),

  (NCHAR(0x2008),'Punctuation Space'),

  (NCHAR(0x2009),'Thin Space'),

  (NCHAR(0x200A),'Hair Space'),

  (NCHAR(0x200B),'Zero Width Space'),

  (NCHAR(0x202F),'Narrow No-Break Space'),

  (NCHAR(0x205F),'Medium Mathematical Space'),

  (NCHAR(0xFEFF) , 'Zero Width No-Break Space');

  SELECTName,

  ASCII(SpaceChar) 'ASCII',

  UNICODE(SpaceChar) 'UNICODE',

  CHAR(34) + RTRIM('A' + SpaceChar) + char(34) 'Trimmed' ,

  ASCII(RIGHT(RTRIM('A' + SpaceChar), 1)) 'Right_ASCII',

  LEN(RTRIM('A' + SpaceChar)) 'TRIMMED_LENGTH'

  FROM@test-2