Using RTRIM in JOIN condition

Question

I want to put two tables which are containing Products together by their DESCRIPTIONs

TABLE 1: [..fields..] [DESCRIPTION1] [..fields..]

TABLE 2: [..fields..] [DESCRIPTION2] [..fields..]

SELECT [..fields..] FROM TABLE1,TABLE2 
    INNER JOIN TABLE1 
    ON TABLE1.DESCRIPTION1 = TABLE2.DESCRIPTION2

But in lots of cases, there are descriptions in both tables ending with an space, i also want to get them, thatswhy i tried it that way:

SELECT [..fields..] FROM TABLE1,TABLE2 
    INNER JOIN TABLE1 
    ON TABLE1.DESCRIPTION1 = TABLE2.DESCRIPTION2 
    OR RTRIM(TABLE1.DESCRIPTION1) = RTRIM(TABLE2.DESCRIPTION2)

Which produces no error, but does not bring more data.

Is anybody able to help me?

Every help is appreciated :)

Thanks in Advance, Harry

Solution 1

The problem might be CRLF characters at the end of the Description, which will look like spaces in SSMS and won't be removed by RTRIM().

To check, see if selecting where Description like '%' + char(13) + char(10) returns anything. If it looks like there is only 1 space at the end of the Description, try either char(13) or char(10) alone. Finally, a Tab (char(9)) would also look like a space, so you could try that as well.

If it does turn out to be any of these characters, you could use REPLACE to get rid of them:

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

which I found here.

Solution 2

You do not need the OR

SELECT [..fields..] 
  FROM TABLE1 T1
       INNER JOIN TABLE2 T2
         ON RTRIM(T1.DESCRIPTION1) = RTRIM(T2.DESCRIPTION2)

Solution 3

Depending on the data type, you shouldn't need RTRIM() at all. I also wonder if your mix of old-style and modern join syntax is tripping you up (though that should yield more rows, not less). What does this yield?

SELECT [..columns..]
    FROM dbo.TABLE1 AS t1
    INNER JOIN dbo.TABLE2 AS t2
    ON RTRIM(t1.DESCRIPTION1) = RTRIM(t2.DESCRIPTION2);