excel - VBA提取字符串中的所有字段名

Question

我有一个这样的字符串:"'where CAST(a.DT_NPE_SORTIE as integer ) < cast (add_months (cast (a.dt_nep_restructuration as date format 'YYYYMMDD'), 12) as integer) and a.DT_NPE_SORTIE is not null and a.DT_NPE_SORTIE <> '99991231' and a.dt_npe_restructuration is not null and a.dt_npe_restructuration <> '99991231'"我需要提取所有 "a.FIELDNAME"喜欢 a.dt_nep_restructuration , a.DT_NPE_SORTIE从上一个屏幕。
我需要在 VBA 中为工作中的项目执行此操作。
到目前为止，我使用了 If & InStr检查字符串中是否存在值列表​​。但是我会更容易提取所有 a.FIELDNAME 然后检查它们是否与数组中的字段名匹配。
此致，
胡夫泽

Answer 1

I saw there are many with "NPE" and one with "NEP"? Is that a typo? If it is, then will it always start with a.dt_nep_...? – Siddharth Rout 9 mins ago

No it's a typo in order to raise an error for my vba function. It will always start with "a." – Jouvzer 6 mins ago

我已经处理了 NPE/NEP。这是你正在尝试的吗？

Option Explicit

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "a.dt_(nep|npe)_\w+"
    Dim regEx As Object
    Dim strInput As String
    Dim inputMatches As Object
    Dim i As Long
    
    strInput = "'where CAST(a.DT_NPE_SORTIE as integer ) < cast (add_months (cast (a.dt_nep_restructuration as date format 'YYYYMMDD'), 12) as integer) and a.DT_NPE_SORTIE is not null and a.DT_NPE_SORTIE <> '99991231' and a.dt_npe_restructuration is not null and a.dt_npe_restructuration <> '99991231'"
        
    Set regEx = CreateObject("VBScript.RegExp")
        
    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = strPattern
    End With
        
    Set inputMatches = regEx.Execute(strInput)
    
    If inputMatches.Count <> 0 Then
        For i = 0 To inputMatches.Count - 1
            Debug.Print inputMatches.Item(i)
        Next i
    End If
End Sub

备注 : 如果它以 a.dt 开头那么你也可以使用 a.dt_\w+_\w+