CODEWITHSUNDEEP
sqlms-access
Peter Kipe
Peter Kipe

Reputation: 97

How to join two tables on fields that are not formatted the same

I have two tables containing a string field that in one table is six bytes long, and in the other table is seven bytes long, because of the insertion of a hyphen between the first two bytes and the last four bytes. The two fields look like this: AB1234 and AB-1234. I tried a join like this:

    FROM       TableA ta
    INNER JOIN TableB tb ON Left(ta.fld, 2) + '-' + Mid(ta.fld, 3) = tb.fld

...and I tried

    FROM       TableA ta
    INNER JOIN TableB tb ON Left(ta.fld, 2) = Left(tb.fld, 2) AND Mid(ta.fld, 3) = Mid(tb.fld, 4)

...but neither works. Is there a way to do this with a subquery? Is there another approach?

Upvotes: 0

Views: 68

Answers (2)

Naveen Kumar
Naveen Kumar

Reputation: 2006

Use RIGHT instead of MID

Left(ta.fld, 2) + '-' + RIGHT(ta.fld, 4) = tb.fld

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

How about using REPLACE to remove the dash before comparing:

SELECT *
FROM TableA a
INNER JOIN TableB b
    ON a.fld = REPLACE(b.fld, "_", "");

Upvotes: 2

Related Questions