VBA: REGEX LOOKBEHIND MS ACCESS 2010

Question

我编写了一个函数，以便VBA可以在MS Access中使用，我希望这样做

我已经设置了下面的代码。产品之前的一切都运行得很完美，但是试图获取背后的信息只是返回"“，这很奇怪，因为当我在Notepad++中执行它时，它工作得非常好。

因此，它寻找的字母MIP和三个字母代码之一(其中任何一个)。

 StringToCheck = "MADHUBESOMIPTDTLTRCOYORGLEJ"

' PART 1
' If MIP appears in the string, then delete any of the following codes if they exist - DOM, DOX, DDI, ECX, LOW, WPX, SDX, DD6, DES, BDX, CMX,
' WMX, TDX, TDT, BSA, EPA, EPP, ACP, ACA, ACE, ACS, GMB, MAL, USP, NWP.
' EXAMPLE 1.  Flagged as: MADHUBESOMIPTDTLTRCOYORGLEJ, should be MADHUBESOMIPLTRCOYORGLEJ


Do While regexp(StringToCheck, "MIP(DOM|DOX|DDI|ECX|LOW|WPX|SDX|DD6|DES|BDX|CMX|WMX|TDX|TDT|BSA|EPA|EPP|ACP|ACA|ACE|ACS|GMB|MAL|USP|NWP|BBX)", False) <> ""
    ' SELECT EVERYTHING BEFORE THE THREE LETTER CODES
     strPart1 = regexp(StringToCheck, ".*^[^_]+(?=DOM|DOX|DDI|ECX|LOW|WPX|SDX|DD6|DES|BDX|CMX|WMX|TDX|TDT|BSA|EPA|EPP|ACP|ACA|ACE|ACS|GMB|MAL|USP|NWP|BBX)", False)
    ' SELECT EVERYTHING AFTER THE THREE LETTER CODES
     strPart2 = regexp(StringToCheck, "(?<=(DOM|DOX|DDI|ECX|LOW|WPX|SDX|DD6|DES|BDX|CMX|WMX|TDX|TDT|BSA|EPA|EPP|ACP|ACA|ACE|ACS|GMB|MAL|USP|NWP|BBX).*", False)
StringToCheck = strPart1 & strPart2
Loop

我从互联网上获取的功能如下

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

On Error GoTo RefErr:

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 = UCase(m.Value)
Next

RefErr:
    On Error Resume Next

End Function

Answer 1

只需分两步做：

1. 检查MIP是否在字符串中
2. 如果是的话，删除其他代码。

如下所示：

Sub Test()
  Dim StringToCheck As String
  StringToCheck = "MADHUBESOMIPTDTLTRCOYORGLEJ"

  Debug.Print StringToCheck
  Debug.Print CleanupString(StringToCheck)
End Sub

Function CleanupString(str As String) As String
  Dim reCheck As New RegExp
  Dim reCodes As New RegExp

  reCheck.Pattern = "^(?:...)*?MIP"
  reCodes.Pattern = "^((?:...)*?)(?:DOM|DOX|DDI|ECX|LOW|WPX|SDX|DD6|DES|BDX|CMX|WMX|TDX|TDT|BSA|EPA|EPP|ACP|ACA|ACE|ACS|GMB|MAL|USP|NWP|BBX)"
  reCodes.Global = True

  If reCheck.Test(str) Then
    While reCodes.Test(str)
      str = reCodes.Replace(str, "$1")
    Wend
  End If

  CleanupString = str
End Function

请注意，(?:...)*?的目的是将字母分成三组。

由于VBScript正则表达式引擎确实支持外观，所以您当然也可以在一个正则表达式中完成它：

Function CleanupString(str As String) As String
  Dim reClean As New RegExp

  reClean.Pattern = "^(?=(?:...)*?MIP)((?:...)*?)(?:DOM|DOX|DDI|ECX|LOW|WPX|SDX|DD6|DES|BDX|CMX|WMX|TDX|TDT|BSA|EPA|EPP|ACP|ACA|ACE|ACS|GMB|MAL|USP|NWP|BBX)"

  While reClean.Test(str)
    str = reClean.Replace(str, "$1")
  Wend

  CleanupString = str
End Function

就我个人而言，我更喜欢两步检查/删除模式，因为它更明显，因此更易于维护。

Answer 2

非RE选项：

Function DeMIPString(StringToCheck As String) As String
    If Not InStr(StringToCheck, "MIP") Then
        DeMIPString = StringToCheck
    Else
        Dim i As Long
        For i = 1 To Len(StringToCheck) Step 3
            Select Case Mid$(StringToCheck, i, 3)
                Case "MIP", "DOM", "DOX", "DDI", "ECX", "LOW", "WPX", "SDX", "DD6", "DES", "BDX", "CMX", "WMX", "TDX", "TDT", "BSA", "EPA", "EPP", "ACP", "ACA", "ACE", "ACS", "GMB", "MAL", "USP", "NWP":
                Case Else
                    DeMIPString = DeMIPString & Mid$(StringToCheck, i, 3)
            End Select
        Next
    End If
End Function