SQL Inner Join for fields when one has an extra character on the end

Question

We have 2 tables with identical fields, but 1 table has an additional space at the end of the field due to our inhouse application adding the space.

The fields are character varying, so I cannot specify a length.

In Access, the following worked with the INNER JOIN, but I cannot get it to work in SSRS: ON table1.field1 + " " = table2.field1

To recap, I need to INNER JOIN 2 fields, but 1 of the fields has an extra space on the right hand/end of the field.

Thanks in advance

Trailing spaces are ignored in equality comparisons in SQL Server. `ON table1.field1 = table2.field1` ought to work fine. As should your original `table1.field1 + " " = table2.field1` actually. — Martin Smith, Aug 27 '14 at 18:13

score 0 · Accepted Answer · edited May 23 '17 at 11:57

Instead of concatenating a space, it seems more solid to trim your results.

ON table1.field1 = rtrim(table2.field1)

To make sure that there are no spaces preceding or appended you can trim everything:

ON ltrim(rtrim(table1.field1)) = ltrim(rtrim(table2.field1))

You can see that at work in this fiddle.

1 Answers1