Microsoft office Access `LIKE` VS `RegEx`

Question

I have been having trouble with the Access key term LIKE and it's use. I want to use the following RegEx (Regular Expression) in query form as a sort of "verfication rule" where the LIKE operator filters my results:

"^[0]{1}[0-9]{8,9}$"

How can this be accomplished?

Answer 1

I know you were not asking about the VBA, but it maybe you will give it a chance

If you open a VBA project, insert new module, then pick Tools -> References and add a reference to Microsoft VBScript Regular Expressions 5.5. Given that pate the code below to the newly inserted module.

Function my_regexp(ByRef sIn As String, ByVal mypattern As String) As String
   Dim r As New RegExp
    Dim colMatches As MatchCollection
    With r
        .Pattern = mypattern
        .IgnoreCase = True
        .Global = False
        .MultiLine = False
        Set colMatches = .Execute(sIn)
    End With
    If colMatches.Count > 0 Then
        my_regexp = colMatches(0).Value
    Else
        my_regexp = ""
    End If
End Function

Now you may use the function above in your SQL queries. So your question would be now solved by invoking

SELECT my_regexp(some_variable, "^[0]{1}[0-9]{8,9}$") FROM some_table

if will return empty string if nothing is matched.

Hope you liked it.