Access 中的 Regex VBA - 在两个字符串之间查找文本

Question

我在 Access VBA 中遇到了正则表达式问题。

我的目标是从链接数据库连接字符串中提取服务器。基本上，连接字符串看起来像

ODBC;DRIVER=SQL Server;SERVER=compName\sqlexpress;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=databaseName

我能够让第一个正则表达式工作，但它正在返回

SERVER=compName\sqlexpress

我希望它只返回

compName\sqlexpress

我的理解是?<=运算符(operator)应该允许 RegEx 正常工作，但我收到以下错误“对象 'IRegExp2' 的方法 'Execute' 失败。”

我能找到的关于任何 Microsoft RegEx 语法的唯一文档是 here这不是运行时 5.5 VBScript 库，但我不确定还能从哪里获得支持的语法。

这是我用来测试它的代码。我的数据库有很多链接表。

Sub printServerStringInformation()
    Dim rxPattern As String

    rxPattern = "(?=SERVER)(.*)(?=;Trusted)"
    Debug.Print RxMatch(CurrentDb.tableDefs(1).Connect, rxPattern, False)

    rxPattern = "(?<=SERVER)(.*)(?=;Trusted)"
    Debug.Print RxMatch(CurrentDb.tableDefs(1).Connect, rxPattern, False)

End Sub

这是我正在使用的函数:

Public Function RxMatch( _
    ByVal SourceString As String, _
    ByVal Pattern As String, _
    Optional ByVal IgnoreCase As Boolean = True, _
    Optional ByVal MultiLine As Boolean = True) As Variant
 'Microsoft VBScript Regular Expressions 5.5

    'http://www.zytrax.com/tech/web/regex.htm#more
    'http://bytecomb.com/regular-expressions-in-vba/

    'http://xkcd.com/1171/
    On Error GoTo errHandler

    Dim oMatches As MatchCollection
    With New RegExp
        .MultiLine = MultiLine
        .IgnoreCase = IgnoreCase
        .Global = False
        .Pattern = Pattern
        Set oMatches = .Execute(SourceString)
        If oMatches.Count > 0 Then
            RxMatch = oMatches(0).value
        Else
            RxMatch = ""
        End If
    End With

errHandler:
    Debug.Print Err.Description

End Function

Answer 1

这里是使用 RegEx 的解决方案(可以转换为函数的完整代码):

Sub qTest_3()

    Dim objRE As New RegExp
    Dim Tekst As String
    Dim Wynik As Variant


    Tekst = "ODBC;DRIVER=SQL Server;SERVER=compName\sqlexpress;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=databaseName"
    With objRE
        .Global = True
        .IgnoreCase = True
        .Pattern = "(^.*;SERVER=)(.*)(;Trusted.*)" 

        Wynik = .Replace(Tekst, "$2")   'only 2nd part of the pattern will be returned

    End With
    Debug.Print Wynik

End Sub

您的功能更改如下(我添加了应该返回的模式的附加参数设置部分):

Public Function RxMatchReturn( _
    ByVal SourceString As String, _
    ByVal Pattern As String, _
    StringPart As Byte, _
    Optional ByVal IgnoreCase As Boolean = True, _
    Optional ByVal MultiLine As Boolean = True) As Variant
    'Microsoft VBScript Regular Expressions 5.5

    On Error GoTo errHandler

    Dim oMatches As MatchCollection
    With New RegExp
        .MultiLine = MultiLine
        .IgnoreCase = IgnoreCase
        .Global = True
        .Pattern = Pattern
        RxMatchReturn = .Replace(SourceString, "$" & StringPart)
    End With

errHandler:
    Debug.Print err.Description

End Function