Access UPDATE query using Regular Expressions

I have created the following Regular expression in javascript to test if the string works.

var str = "Test [abc] =1234= (PG1/2)";
var pattern = /[[]/; 
var pattern1 = /[]]/; 
var pattern2 = /[=?]/;
var pattern3 = /[(]+[A-z0-9/]+[)]/;
var result = str.replace(pattern1, "").replace(pattern,"").replace(pattern2, "[").replace(pattern2,"]").replace(pattern3,"");

which works perfectly fine. However no i have tried to convert this SQL (using the ms access sample database)

i have tried the following which hasn’t worked as i get an error.

UPDATE Orders
SET Orders.ShipName=replace(replace( Orders.ShipName, "/[[]/", ''), "/[]]/",'');

which didn’t work. I think I’ve gone about the wrong approach to the SQL. can anyone advise on the correct way to do this? as all of the replaces are required to be executed on the Orders.ShipName field. Is this even possible?

Answer

The standard Replace() function in Access does not support pattern matching using regular expressions, but you could “roll your own” in VBA quite easily:

Option Compare Database
Option Explicit

Public Function MyRegexReplace( _
        originalText As Variant, _
        regexPattern As String, _
        replaceText As String) As Variant
    ' VBA Project Reference required:
    '  Microsoft VBScript Regular Expressions 5.5
    Dim rtn As Variant
    Dim objRegExp As RegExp, objMatch As Match, colMatches As MatchCollection

    rtn = originalText
    If Not IsNull(rtn) Then
        Set objRegExp = New RegExp
        objRegExp.pattern = regexPattern
        Set colMatches = objRegExp.Execute(originalText)
        For Each objMatch In colMatches
            rtn = _
                    Left(rtn, objMatch.FirstIndex) & _
                    replaceText & _
                    Mid(rtn, objMatch.FirstIndex + objMatch.length + 1)
        Next
        Set objMatch = Nothing
        Set colMatches = Nothing
        Set objRegExp = Nothing
    End If
    MyRegexReplace = rtn
End Function

Some VBA test code for the example in your question would be

Private Sub testcode()
    Dim str As String
    Dim pattern As String, pattern1 As String, pattern2 As String, pattern3 As String
    Dim result As String

    str = "Test [abc] =1234= (PG1/2)"
    pattern = "[[]"
    pattern1 = "[]]"
    pattern2 = "[=?]"
    pattern3 = "[(]+[A-z0-9/]+[)]"
    result = str
    result = MyRegexReplace(result, pattern1, "")
    result = MyRegexReplace(result, pattern, "")
    result = MyRegexReplace(result, pattern2, "[")
    result = MyRegexReplace(result, pattern2, "]")
    result = MyRegexReplace(result, pattern3, "")

    Debug.Print result  ' Test abc [1234]
End Sub

If you needed to perform multiple “inline” replacements in a query you could still nest the calls to your function as you did before:

UPDATE Orders
SET ShipName = MyRegexReplace(MyRegexReplace(ShipName, "[]]", ""), "[[]", "")

For more details on Microsoft Regular Expressions see:

How To Use Regular Expressions in Microsoft Visual Basic 6.0