Why wont Rtrim clear spaces (for comparison)

Question

I have a a col that is a text field (i know not used any more) that i need to compare. (the instruction field is a text field) Case when rtrim(cast(RT.INSTRUCTIONS as varchar(max))) = rtrim(cast(HQ.INSTRUCTIONS as varchar(max))) then 'TRUE' Else 'FALSE' end as INSTRUCTIONS.

the value in RT.Instructions is "Check the oil levels every 30 hours. " the value in HQ.Instructions is "Check the oil levels every 30 hours."

Why wont the trailing blank go away. i did a len on both and hq is 1 less then the rt value. I also am having the same issue on a varchar(60) field.

Are you sure it is a space char(32) and not some other control character? — John Cappelletti, Apr 27 '17 at 18:26
I am not seeing any values other then "Blank""Empty" spaces on the end — Lee Hopkins, Apr 27 '17 at 18:33
replace " " with "" and see if you still have the blank spaces.. if you do it's not a space but some other non-display character... you can figure it out by using ASCII on each character after the period. could be a tab, hard return etc... — xQbert, Apr 27 '17 at 19:36

score 0 · Accepted Answer · answered Apr 27 '17 at 19:14

Perhaps there is a character that isn't being picked up. Maybe the following will be useful in finding that value. Or maybe just get you started down the right path.

DECLARE @Char INT = 0
DECLARE @Tab TABLE (Id INT, Chr VARCHAR(5), Instructions VARCHAR(MAX), c VARCHAR(MAX))
WHILE @Char < = 256
BEGIN
INSERT INTO @Tab
SELECT Id
      ,CONVERT(NVARCHAR,CHAR(@Char)) Chr
      ,CONVERT(NVARCHAR,RIGHT(RTRIM(rt.Instructions),1)) InstructionChar
      ,CONVERT(NVARCHAR,CHAR(CONVERT(int,@Char))) c
FROM YourTable
WHERE RIGHT(RTRIM(Instructions),1) LIKE '%'+CHAR(CONVERT(int,@Char))
  AND RIGHT(RTRIM(Instructions),1) NOT LIKE '[A-Za-z]'
  AND RIGHT(RTRIM(Instructions),1) NOT LIKE '[0-9]'
  AND RIGHT(RTRIM(Instructions),1) NOT LIKE '.'
  AND RIGHT(RTRIM(Instructions),1) NOT LIKE ']'
  AND RIGHT(RTRIM(Instructions),1) NOT LIKE ')'
  AND RIGHT(RTRIM(Instructions),1) NOT LIKE '"'
  AND RIGHT(RTRIM(Instructions),1) NOT LIKE '}'
  AND RIGHT(RTRIM(Instructions),1) NOT LIKE '/'
SET @Char = @Char + 1
END
SELECT DISTINCT *
FROM @Tab

score 0 · Answer 2 · answered Apr 27 '17 at 21:22

Sorry : I'm not allowed to write comments yet

First try :

Case when left(rtrim(cast(RT.INSTRUCTIONS as varchar(max))),len(HQ.INSTRUCTIONS)) = rtrim(cast(HQ.INSTRUCTIONS as varchar(max))) then 'TRUE' Else 'FALSE' end as INSTRUCTIONS

To check if no other issue is concerned.

Then do :

SELECT ASCII(right(RT.INSTRUCTIONS,1))

To confirm that the trailling space is a "real" space : this query should display 32.

CHAR(32) => ' '

ASCII (' ') => 32

I bet you will get 16O. 160 means the last caracter is an non-breaking space witch is not concerned by trim functions...

If so, you will have to build a scalar function like :

ALTER FUNCTION [dbo].[fn_Replace_NonBreakingSpace]
(
    @InputString varchar(max),
)
RETURNS varchar(MAX)
AS
BEGIN
    RETURN REPLACE(@InputString, char(160), char(32))
END

And then :

Case when rtrim(dbo.fn_Replace_NonBreakingSpace(RT.INSTRUCTIONS)) = rtrim(dbo.fn_Replace_NonBreakingSpace(HQ.INSTRUCTIONS)) then 'TRUE' Else 'FALSE' end as INSTRUCTIONS

Why wont Rtrim clear spaces (for comparison)

2 Answers2