RTRIM is not working as expected

Question

Post reply

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

Viewing 15 posts - 1 through 15 (of 30 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

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 · Answer 2

Hi,

here a snap-shot from management studio:

Thanks for the quick answer!

Paul Hernández

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

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 · Answer 4

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 · Answer 5

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 · Answer 6

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 · Answer 7

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 · Answer 8

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 · Answer 9

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 · Answer 10

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 · Answer 11

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 · Answer 12

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 · Answer 13

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 · Answer 14

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