特定的VBA / VBScript正则表达式相关问题（MS Access） [英] Specific VBA / VBScript regex related issue (MS Access)

问题描述

在创建了 http://vbfiddle.net 以在浏览器中实施和测试@ctwheel的VBScript解决方案（MS IE 10+（安全性设置为中），说明在该网站上了解如何设置它以供您玩-从jsfiddle.net上的以下链接获取要复制并粘贴到vbfiddle.net的代码： https://jsfiddle.net/hcwjhmg9/ [ vbfiddle.net当前不具有保存功能]），我发现即使对于我给出的第三个示例行，@ctwheel的VBScript RegEx也成功运行，但是当针对VBA for Microsoft Access 2016在VBScript中使用@ctwheel的VBScript RegEx针对带有相同值的数据库读取的记录时，第三个子组只返回 Ray，这是我给的第三个示例行，应该返回 Ray，CFP

After creating a website called http://vbfiddle.net to implement and test @ctwheel's VBScript solution in a browser (MS IE 10+ with Security set to "Medium", instructions on that website for how to set it up for you to play with should you want -- get the code to copy & paste into vbfiddle.net from this link at jsfiddle.net: https://jsfiddle.net/hcwjhmg9/ [vbfiddle.net does not currently have a "save" feature] ), I found that @ctwheel's VBScript RegEx ran successfully, even for the 3rd example line I gave, but when @ctwheel's VBScript RegEx is used in VBScript for VBA for Microsoft Access 2016 against a record read from a database with the "same" value, the third subgroup only returns "Ray," for the 3rd example line I gave, when it should return "Ray, CFP" like it does in vbfiddle.net.

我终于想到要遍历数据库返回的字符串值的每个字符（在Microsoft Access的VBA中），然后进行比较我直接在代码中键入（在Microsoft Access中的VBA中）视觉等效字符串值的每个字符的迭代。我得到以下结果：

It finally occurred to me to iterate through every character of the string value returned by the database (in VBA in Microsoft Access), and compare it to an iteration of every character of the visually-equivalent string value I type directly into the code (in VBA in Microsoft Access). I get the following results:

First Name and Last Name: "G.L. (Pete) Ray, CFP"
--- 1st Text chars: "71 46 76 46 32 40 80 101 116 101 41 32 82 97 121 44" 
(Read value from database, appears same as below when Debug.Print is called on it)
--- 2nd Text chars: "71 46 76 46 32 40 80 101 116 101 41 32 82 97 121 44 32 67 70 80" (Typed by keyboard into a string within the code)
'G.L. (Pete) Ray,'
    strProperName>objSubMatch: "G.L."
    strProperName>objSubMatch: "Pete"
    strProperName>objSubMatch: "Ray,"
 Matching record(s): 3 of 1132 record(s).

我正在运行的RegEx正在针对 第一文字字符示例，并为先前给定的第3个示例行的第3个子组返回 Ray： GL（Pete）Ray，CFP 。但是，如果我对第二个运行RegEx（直接输入代码） 第二个文本字符示例，则第三个子组将按预期返回 Ray，CFP Microsoft Access 2016的VBA。

The RegEx I'm running is running against the "1st Text Chars" example, and returns "Ray," for the 3rd subgroup of the previously given 3rd example line: "G.L. (Pete) Ray, CFP". However, if I run the RegEx against the 2nd -- typed directly into code -- "2nd Text chars" example, the 3rd subgroup returns "Ray, CFP" as expected in VBA for Microsoft Access 2016.

我现在使用@ctwheels提供的RegEx：

I'm now using the RegEx that @ctwheels provided:

^([^(]+?)\s*\(\s*([^)]*?)\s*\)\s*(.*)

有人可以解释这里发生了什么吗？ 1）为什么从数据库返回的字符与通过目视读取和复制使用键盘输入字符串所返回的字符不同？ 2）如何制作可在 第一个文本字符字符/字符串序列上使用的RegEx返回正确的第三个子组： Ray， CFP 是直接从数据库中读取值吗？

Can someone explain what's going on here? 1) Why are the characters returned from the database different from the characters returned from typing the string using a keyboard by reading and copying it visually? 2) How do I make a RegEx that works on the "1st Text Chars" sequence of characters / string return the correct 3rd subgroup: "Ray, CFP" when the value is read directly from the database?

原始问题（上述更新的问题）：

ORIGINAL QUESTION (updated question above):

我在使用Microsoft Access 2016和Regex Engine的VBA中遇到问题，我相信VBScript 5.5。

I'm having problems in VBA using Microsoft Access 2016 with Regex Engine I believe 5.5 for VBScript.

这是我当前使用的正则表达式：

This is the regex expression I'm currently using:

"(.*)\((.*)(\))(.*)"

我正在尝试解析字符串（分别在每个新行上）：

I'm trying to parse the strings (respectively on each new line):

Lawrence N. (Larry) Solomon
James ( Jim ) Alleman
G.L. (Pete) Ray, CFP

进入：

"Lawrence N.", "Larry", ")", "Solomon"
"James", "Jim", ")", "Alleman"
"G.L.", "Pete", ")", "Ray, CFP"

或者（优选）i nto：

Or alternatively (and preferably) into:

"Lawrence N.", "Larry", "Solomon"
"James", "Jim", "Alleman"
"G.L.", "Pete", "Ray, CFP"

引号中用逗号分隔的部分是子匹配中返回的部分（不带引号）

where the parts within the quotes, separated by commas, are those returned in the submatches (without quotes)

我正在使用以下代码：

           ' For Proper Name (strProperName):
            With objRegex
                .Global = False
                .MultiLine = False
                .IgnoreCase = True
                .Pattern = "(.*)\((.*)(\))(.*)"

                    '([\s|\S]*) work around to match every character?

                        '".*\(([^\s]*)[\s]*\(" '_
                        ''& "[\"
                        '[\(][\s]*([.|^\w]*)[\s]*\)"
                    ' "[\s]*(.*)[\s]*\("
                        ' does same as below except matches any or no whitespace preceding any characters,
                        ' and returns the following characters up to an opening parenthesis ("(") but excluding it,
                        ' as the first subgroup
                    ' "(.*)[\s]*\("
                        ' does same as below except matches any whitespace or no whitespace at all followed by an opening parenthesis ("(")
                        ' and returns the preceding characters as the first subgroup
                    ' "(.*)\("
                        ' matches all characters in a row that end with an open parenthesis, and returns all of these characters in a row
                        ' excluding the following open parenthesis as the first subgroup
                    ' "(.*?\(\s)"
                    ' "[^\(]*"
                        ' this pattern returns every character that isn't an opening parenthesis ("("), and when
                        ' it matches an open parenthesis, it does not return it or any characters after it
                    ' "[\(\s](.*)\)"
                        ' this pattern extracts everything between parenthesis in a line as its first submatch
                    ' "(?<=\().*"
                    ' "[^[^\(]*][.*]"
                    ' "(\(.*?\))"
                    ' "(\(.*?\))*([^\(].*[^\)])"
            End With

            If objRegex.Test(strFirstNameTrimmed) Then
                'Set strsMatches = objRegex.Execute(rs.Fields("First Name"))
                Set strsMatches = objRegex.Execute(strFirstNameTrimmed)



                Debug.Print "2:'" & strsMatches(0).Value & "'"

                If strsMatches(0).SubMatches.Count > 0 Then

                    For Each objSubMatch In strsMatches(0).SubMatches

                        Debug.Print "    strProperName>objSubMatch: """ & objSubMatch & """" 'Result: 000, 643, 888"

                        strProperName = objSubMatch

                    Next objSubMatch

                End If
            Else
                strProperName = "*Not Matched*"
            End If

产生以下输出在调试窗口/在VBA中称为立即窗口的情况下，由（Ctrl + G）调出：

Produces the following output in the debug window / "Immediate Window" as it's known in VBA, brought up by (Ctrl+G):

------------------------
First Name and Last Name: "Lawrence N. (Larry) Solomon"
2:'Lawrence N. (Larry)'
    strProperName>objSubMatch: "Lawrence N. "
    strProperName>objSubMatch: "Larry"
    strProperName>objSubMatch: ")"
    strProperName>objSubMatch: ""
Extracted Nick Name: "Larry"
Extracted Proper Name: ""
First Name and Last Name: "James ( Jim ) Alleman"
2:'James ( Jim )'
    strProperName>objSubMatch: "James "
    strProperName>objSubMatch: " Jim "
    strProperName>objSubMatch: ")"
    strProperName>objSubMatch: ""
Extracted Nick Name: "Jim"
Extracted Proper Name: ""
First Name and Last Name: "G.L. (Pete) Ray, CFP"
2:'G.L. (Pete) Ray,'
    strProperName>objSubMatch: "G.L. "
    strProperName>objSubMatch: "Pete"
    strProperName>objSubMatch: ")"
    strProperName>objSubMatch: " Ray,"
Extracted Nick Name: "Pete"
Extracted Proper Name: " Ray,"
Matching record(s): 3 of 1132 record(s).

推荐答案

在此处查看正在使用的正则表达式

^([^(]+?)\s*\(\s*([^)]*?)\s*\)\s*(.*)

• ^ 在行的开头声明位置


• （[^（] +？ ）捕获除（以外的任何字符一次或多次，但尽可能少地捕获到捕获组1


• \s * 匹配任意数量的空格字符


• \（匹配（从字面上


• \s * 匹配任意数量的空格字符


• （[^）] *？）捕获除）一次，但尽可能少，进入捕获组2


• \s * 匹配任意数量的空白字符


• \（匹配（字面意思


• \s * 匹配任意数量的空格字符


• （。*）将行的其余部分捕获到捕获组3


• ^ Assert position at the start of the line
• ([^(]+?) Capture any character except ( one or more times, but as few as possible, into capture group 1
• \s* Match any number of whitespace characters
• \( Match ( literally
• \s* Match any number of whitespace characters
• ([^)]*?) Capture any character except ) one or more times, but as few as possible, into capture group 2
• \s* Match any number of whitespace characters
• \( Match ( literally
• \s* Match any number of whitespace characters
• (.*) Capture the rest of the line into capture group 3

结果：

["Lawrence N.", "Larry", "Solomon"]
["James", "Jim", "Alleman"]
["G.L.", "Pete", "Ray, CFP"]

这篇关于特定的VBA / VBScript正则表达式相关问题（MS Access）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！