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.

Quote:

Originally Posted by ChipR

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.

Expand|Select|Wrap|Line Numbers

 
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.

Regular Expressions in SQL

re: Regular Expressions in SQL

re: Regular Expressions in SQL

re: Regular Expressions in SQL

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about