CODEWITHSUNDEEP
sqlsql-serverms-access
user9531726
user9531726

Reputation: 23

how do I join two tables with different fields?

I have two tables that I would like to join on the following fields

Table A

ID
2345
2346
2347

TableB

BarecodeID
*2345U12*
*2346P1*
*2346P2*
*2346P3*
*2347Z1*
*2347Z2*

TableB.BarCodeID was generated from TableA.ID earlier in front-end access by concatenating

TableB.BarcodeID = '*' & TableA.ID & 'Z' & x & '*'

so in order to join I would like to trim, extract, etc!!!!! off the "*" from the ends and trim off the Characters and numbers that follow (e.g U12, P1, P2, etc).

Please advise on best practice to join two table of similar fields.

Upvotes: 0

Views: 56

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272056

Something LIKE:

SELECT *
FROM TableA
INNER JOIN TableB ON TableB.BarecodeID LIKE CONCAT('*', TableA.ID, '[A-Z]%')

This assumes that the numbers are always followed by a character. So 2345 will match *2345WHATEVER but not *23456WHATEVER.

Upvotes: 4

Related Questions