How to run Regex in MSaccess (If supported)?

Question

This is my first post, please be nice.

I want to know if it is possible to run similar to an Oracle regexp_match/regexp_replace in MSaccess 2010.

The code I usually use is something like select * from table where regexp_match(name, '^foo$')

How do I do this in Access, Yes I have tried google search, unfortunately I was unsuccessful.

Do I have to use VBA for it, if so how?

Thank you, much appreciated

I don't know of this function is usable from queries: https://stackoverflow.com/questions/37443992/implementing-regex-into-access-vba-for-password-complexity — shawnt00, Mar 21 '18 at 00:56
I disagree that the referenced questions are duplicates. While they show examples of the RegEx object in VBA they don't tie that back to calling it from a query such that it serves as a replacement for the Oracle regexp_match/regexp_replace functions as the OP asks. — andrew, Mar 22 '18 at 06:22
What is missing from the other answer (supposedly a duplicate) is that you can wrap calls to the VBScript Regular Expression object in a public function in a module and call that function from a query. The caveat is that it will not be able to use indices (it will scan the whole table and call the function for every value not filtered out by other parts of your WHERE clause). I also recommend defining the RegExp object as a static variable and only create it if not already set (I'd give example code but the question is locked). — andrew, Mar 22 '18 at 07:06

score 0 · Answer 1 · answered Mar 21 '18 at 00:52

0

MS Access does not have built-in regular expression support.

However, this query:

select t.*
from table t
where regexp_match(t.name, '^foo$')

Is better written as:

select t.*
from table t
where t.name = 'foo';

Equality is more efficient in any database.

answered Mar 21 '18 at 00:52

Gordon Linoff

Hi Gordon, thank you for answering my question. If ms-access doesnt support regex can it be imported via a vba or done in excel. Also I do know how to regex the above, just unsure how to do so in access. foo was an example. – user9525789 Mar 21 '18 at 00:57

1 Answers1