topic pattern matching in ms access query in SQL database

pattern matching in ms access query

vijaykaali811 — Wed, 25 Jun 2025 10:01:54 GMT

i have table T1 with query, name

T2 table pattern , category

Query can be anything

t2 table

pattern2 category

connection*failure* connectivity

power*failure* power issue

i am trying like

select t1.query, t2.patter,t2.category from t1,t2 where query like "*"&t2.pattern2&"*"

but in MS access it says join does not suppport expression . how to achieve this.

Re: pattern matching in ms access query

v-lgarikapat — Thu, 26 Jun 2025 06:36:07 GMT

Hi vijaykaali811 ,

Thanks for reaching out to the Microsoft fabric community forum.

Thanks for sharing your question here Since your issue seems to be related specifically to pattern matching in MS Access queries, I’d recommend also posting this thread in the Microsoft Access Community. There are many experienced Access developers and experts there who are more focused on Access-specific syntax and behaviour they may be able to offer more targeted assistance.

Database Software and Applications | Microsoft Access

Best Regards,

Lakshmi Narayana

Re: pattern matching in ms access query

Shahid12523 — Mon, 01 Sep 2025 08:03:48 GMT

In MS Access, you can’t use LIKE in the join.

Do this instead:

SELECT t1.query, t2.pattern2, t2.category
FROM t1, t2
WHERE t1.query LIKE "*" & t2.pattern2 & "*";

👉 Put LIKE in the WHERE clause, not the join.

Re: pattern matching in ms access query

Avyaktha — Thu, 02 Oct 2025 15:54:03 GMT

hi vijaykaali811

In MS Access, you can't use a pattern match (LIKE "*"&...&"*" ) as part of a join. Access doesn't allow expressions like that in joins.

But you can still do what you want by writing the query like this:

SELECT t1.query, t2.pattern2, t2.category
FROM t1, t2
WHERE t1.query LIKE "*" & t2.pattern2 & "*";

This is how it will work :
It compares every row in t1 with every row in t2, and filters them where the pattern matches.

BUT If t2.pattern2 has * inside (like connection*failure*), that won’t work as you expect.
Access uses * as a wildcard, not as a separator. So instead you can store just simple text like connection or failure

Or split pattern2 into multiple columns if you want to check multiple words.

Thank you
Avyaktha