Regular Expressions in SQL

[NeoPa]

I looked today for some link that explained what Regular Expressions were available for use in a SQL WHERE clause (using the Like operator), but was unable to find anything related.

I was able to find records where the Product_Code matched "P" & "L" or "P" & 4 numeric digits by specifying "P[LP][0-9][0-9][0-9][0-9]". This works perfectly, but I feel there ought to be a way of saying "[0-9]" & 3 times the last one (This is available in Posix and other systems using Regular Expressions).

If anyone can throw any light, or even supply links to the relevant information, then I'd be grateful.

[ChipR]

Finally found my old post on this. I didn't ever find an alternative, but it does work nicely.

I have good news. There is a way to do this. You'll just have to play with this to see. You could make the funciton take a string to search and a string to match and return a boolean. Here's a quick reference to regular expresions so you can try some out. Note that this uses late binding, and if you used it extensively you might not want to.

Public Function MatchRegEx()

    Dim RE As Object
    Dim allMatches As Object
    Dim strPattern As String
    Dim strFindIn As String
    
    strPattern = ".*(color|colour).*"
    strFindIn = "the color red"
    
    Set RE = CreateObject("vbscript.regexp")
    RE.Pattern = strPattern
    RE.IgnoreCase = True
    RE.Global = True
    Set allMatches = RE.Execute(strFindIn)
    If allMatches.count > 0 Then
        MsgBox "match"
    Else
        MsgBox "no match"
    End If

End Function

"the color red" matches
"the colour red" matches
"the coloor red' doesn't

[NeoPa]

Thanks for that Chip.

I'm looking for RegExps that work in Jet SQL specifically, but that link is helpful nevertheless. I was certainly unaware of such functionality being available in VBA (even if via 3rd party software).

I plan to test out some of the ideas in there and see if they are supported by Jet SQL too. I know some of the basic ones are, so there may be more of them, although I'm pretty sure ? & * have different meanings within Jet SQL. I'll post back what I discover.

[NeoPa]

Actually, triggered by your post Chip, I did some digging of my own (through my own old stuff), and came up with something which is helpful, but could possibly have omitted some extra stuff I never knew about I suppose. It's something I posted some while back (ANSI Standards in String Comparisons).

My comparison string has now been changed to Like 'P[LP]####'. While this is not perfect, it's certainly better than I started with.