Daily Archives: 08/08/2014

Using Like to Join Tables (MS Access)

Key:

Use Regular Express
Use Instr function

Example:

SELECT *
FROM current_table AS a 
INNER JOIN new_table AS b ON instr(b.item_key,regexp(a.item_key,&quot;[0-9]+&quot;))&gt;0;

1

2

3

SELECT *

FROM current_table AS a

INNER JOIN new_table AS b ON instr(b.item_key,regexp(a.item_key,"[0-9]+"))>0;

Reference:

Access doesn't provide Regular Expression function by default. Following these steps to setup:
http://bricestacey.com/2010/07/09/Regular-Expressions-in-MS-Access.html
Instr:
http://stackoverflow.com/questions/1386166/how-to-use-a-like-with-a-join-in-sql