sql - 使用正则表达式 Access UPDATE 查询

Question

我在 javascript 中创建了以下正则表达式来测试字符串是否有效。

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,"");

效果很好。但是我没有尝试转换此 SQL(使用 ms access 示例数据库)

我已经尝试了以下方法，但由于出现错误而无法正常工作。

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

这没有用。我认为我对 SQL 采取了错误的方法。谁能建议正确的方法来做到这一点？因为所有替换都需要在 Orders.ShipName 字段上执行。这可能吗？

Answer 1

标准Replace() Access 中的函数不支持使用正则表达式进行模式匹配，但您可以很容易地在 VBA 中“自己动手”:

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

您问题中示例的一些 VBA 测试代码是

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

如果您需要在查询中执行多个“内联”替换，您仍然可以像以前一样嵌套对函数的调用:

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

有关 Microsoft 正则表达式的更多详细信息，请参阅:

How To Use Regular Expressions in Microsoft Visual Basic 6.0