Microsoft VBS正则表达式访问查询

Question

我正在尝试从存储在MS Access Query列中的文本字符串中替换SSN。评论字段是自由文本，我需要将标准SSN（即123-45-6789)，当存在时，替换为XXX-XX-XXXX。

例如，我希望“JOHN DOE，111-11-1111REWARD 6 DOL已生成。提交选择应在07-FEB-35之前完成请遵守”变为“JOHN DOE， XXX-XX-XXXX REWARD 6 DOL已生成。提交选择应在07-FEB-35之前完成请遵守”

我在replace函数中使用了一个公共函数来完成任务，但我的模式没有按预期工作。访问查询条件和RegexMathc函数：

Expr1: Replace([Comments],RegexMatch([Comments],"\d{3}\-\d{2}\-\d{4}"),"XXX-XX-XXXX")

Public Function RegexMatch(value As Variant, pattern As String) As Boolean
    If IsNull(value) Then Exit Function
    ' Using a static, we avoid re-creating the same regex object for every call '
    Static regex As Object
    ' Initialise the Regex object '
    If regex Is Nothing Then
        Set regex = CreateObject("vbscript.regexp")
        With regex
            .Global = False
            .IgnoreCase = False
            .Multiline = False
            
        End With
    End If
    ' Update the regex pattern if it has changed since last time we were called '
    If regex.pattern <> pattern Then regex.pattern = pattern
    ' Test the value against the pattern '
    RegexMatch = regex.Test(value)
End Function

当我在regex101中尝试正则表达式模式时，它会毫无问题地找到SSN字符串，但它不会出现在我的访问查询列中。

Answer 1

您的正则表达式匹配返回一个布尔值，要么为True，要么为False。

这意味着，Replace被称为这样的东西：

Replace("JOHN DOE,111-11-1111, True reward", True, "XXX-XX-XXXX")

以下内容将被退回：

"JOHN DOE,111-11-1111, XXX-XX-XXXX reward"

您需要在单个函数中进行匹配和替换：

Public Function RegexReplace(value As Variant, Pattern As String, Replace As String) As String
    If IsNull(value) Then Exit Function
    ' Using a static, we avoid re-creating the same regex object for every call '
    Static regex As Object
    ' Initialise the Regex object '
    If regex Is Nothing Then
        Set regex = CreateObject("vbscript.regexp")
        With regex
            .Global = True 'We want to replace all occurrences
            .IgnoreCase = False
            .MultiLine = False
            
        End With
    End If
    ' Update the regex pattern if it has changed since last time we were called '
    If regex.Pattern <> Pattern Then regex.Pattern = Pattern
    ' Replace using the pattern
    RegexReplace = regex.Replace(value, Replace)
End Function