Trimming that "untrimmable space"
UPDATE: It seems Community Server stripped the actual CHAR(160) in @someStringWithPad, just so that it will be contrived append CHAR(160) at the @someStringWithPad variable. ;)
Ever experienced trying the following code to trim a seemingly "space" padded string?
DECLARE @someStringWithPad VARCHAR(25)
SET @someStringWithPad = 'space the final frontier '
SET @someStringWithPad = RTRIM(@someStringWithPad)
IF LEN(@someStringWithPad) > 24
PRINT 'ASSERT: expected 24 characters but was ' + CAST(LEN(@someStringWithPad) AS VARCHAR(3))
Guess what will be printed? It will be the ASSERT message. Now what gives here? We've already RTRIMed the string, trying LTRIM(RTRIM()) won't help either. Doing a REPLACE(@someStringWithPad, SPACE(1),'') , doesn't help either.
Try doing the following:
SELECT ASCII(RIGHT(@someStringWithPad,1))
What do you get? It won't be CHAR(32) for space, it will be CHAR(160) for a Non-breaking Space.
So the fix is to issue a REPLACE(@someStringWithPad, CHAR(160), '') a little tricky if you ask me, and hopefully will save you some headscratching as to why your INNER JOIN or WHERE clauses doesn't match those that is padded with CHAR(160).