Brice Stacey home

Regular Expressions in MS Access

I spend most of my time writing SQL queries for Oracle and I've gotten used to functions like REGEXP_LIKE. Lately, however, I have been training a work-study student how to use MS Access for reporting and no such function seemed to exist. I generally try to shy away from delving into advanced MS Access features, but my works-study needs regular expressions and I want my work-study to learn. So, I scrounged the internet and came up with this.

regexp(<string>, <pattern> [, case sensitive = true])
returns the matched string, null otherwise.

regexp("UMS00123456", "UMS[0-9]{8}") would return UMS00123456
regexp("00123456", "UMS[0-9]{8}") would return null

Configuring MS Access

In Access press ALT-F11. Click Insert > Module. Click Tools > References then tick "Microsoft VBScript Regular Expressions 5.5" Then paste the following into the module:

Option Compare Database
Option Explicit

Function regexp( _
  StringToCheck As Variant, _
  PatternToUse As String, _
  Optional CaseSensitive As Boolean = True)

  Dim re As New regexp
  re.Pattern = PatternToUse
  re.Global = False
  re.IgnoreCase = Not CaseSensitive
  Dim m
  For Each m In re.Execute(StringToCheck)
    regexp = m.Value
End Function

Enjoy. Let me know if there is a better way.