12

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?

Curtis Inderwiesche
  • 4,940
  • 19
  • 60
  • 82
Tamir
  • 123
  • 1
  • 1
  • 4
  • RegEx is not going to be efficiently usable in SQL in Access/Jet/ACE. It's not built into the database engine, so it won't use indexes. It's not part of VBA, either, though you can use the RegEx from the File System Object if you really want to. But for validation rules (I don't know anything else you could mean for "varification rule"), you can't use RegEx at all. You could, however, use the BeforeUpdate event of the control you're using to edit and then use the RegEx function from the File System Object to test the value in the control's Text property, and act accordingly. – David-W-Fenton Apr 07 '11 at 03:48

2 Answers2

27

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.

MPękalski
  • 6,873
  • 4
  • 26
  • 36
  • Hello, I have one small issue with this method, it also returns true for NULL fields. Not a big problem, I can work around this, but I was curious why this happens. – Cornel Jun 23 '16 at 21:23
10

I don't think Access allows regex matches (except in VBA, but that's not what you're asking). The LIKE operator doesn't even support alternation.

Therefore you need to split it up into two expressions.

... WHERE (Blah LIKE "0#########") OR (Blah LIKE "0########")

(# means "a single digit" in Access).

Tim Pietzcker
  • 328,213
  • 58
  • 503
  • 561