将基本Access查询条件表示为正则表达式 [英] Expressing basic Access query criteria as regular expressions
问题描述
我熟悉Access的查询和过滤条件，但是我不确定如何将类似的语句表示为正则表达式模式.我想知道是否有人可以帮助将他们与我理解的一些简单示例联系起来.
I'm familiar with Access's query and filter criteria, but I'm not sure how to express similar statements as regular expression patterns. I'm wondering if someone can help relate them to some easy examples that I understand.
如果我使用正则表达式匹配Access之类的字段，我将如何表达以下语句?示例与在访问查询和过滤条件网页上找到的示例相似.与Access一样，区分大小写.
If I were using regular expressions to match fields like Access, how would I express the following statements? Examples are similar to those found on this Access Query and Filter Criteria webpage. As in Access, case is insensitive.
-
伦敦"
"London"
与伦敦一词完全匹配的字符串.
Strings that match the word London exactly.
伦敦"或巴黎"
与伦敦或巴黎一词完全匹配的字符串.
Strings that match either the words London or Paris exactly.
不是伦敦"
除了伦敦以外的任何字符串.
Any string but London.
像"S *"一样
任何以字母s开头的字符串.
Any string beginning with the letter s.
像"* st"一样
任何以字母st结尾的字符串.
Any string ending with the letters st.
像"*狗*"一样
任何包含单词"the"和"dog"的字符串，在其之前，之间或末尾包含任何字符.
Any strings that contain the words 'the' and 'dog' with any characters before, in between, or at the end.
就像"[A-D] *"
Like "[A-D]*"
任何以字母A到D开头的字符串，其后都是其他字符串.
Any strings beginning with the letters A through D, followed by anything else.
不像"*伦敦*"
任何不包含伦敦字的字符串.
Any strings that do not contain the word London anywhere.
不喜欢"L *"
任何不以L开头的字符串.
Any strings that don't begin with an L.
像"L *"，而不像伦敦*"
Like "L*" And Not Like "London*"
任何以字母L开头但不包含伦敦词的字符串.
Any strings that begin with the letter L but not the word London.
推荐答案
Regex的功能比您用来在Access SQL中创建条件的任何模式都要强大.如果将自己限制为这些类型的模式，则将错过正则表达式的许多真正有趣的功能.
Regex as much more powerful than any of the patterns you have been used to for creating criteria in Access SQL. If you limit yourself to these types of patterns, you will miss most of the really interesting features of regexes.
例如，您无法搜索日期或提取IP地址，简单的电子邮件或URL检测或验证，基本参考代码验证(例如询问订单参考"代码是否遵循规定的编码结构)之类的内容，例如例如PO123/C456)等.
For instance, you can't search for things like dates or extracting IP addresses, simple email or URL detection or validation, basic reference code validation (such as asking whether an Order Reference code follows a mandated coding structure, say something like PO123/C456 for instance), etc.
正如@Smandoli所提到的，您最好忘记对模式匹配的先入之见，而深入研究正则表达式语言.
As @Smandoli mentionned, you'd better forget your preconceptions about pattern matching and dive into the regex language.
我发现这本书精通正则表达式是无价的，但是最好的工具是自由使用正则表达式进行实验的最佳工具模式;我使用 RegexBuddy ，但是有
I found the book Mastering Regular Expressions to be invaluable, but tools are the best to experiment freely with regex patterns; I use RegexBuddy, but there are other tools available.
现在，关于您的列表，并使用相当标准化的正则表达式语法:
Now, regarding your list, and using fairly standardized regular expression syntax:
-
伦敦"
"London"
与伦敦一词完全匹配的字符串.
Strings that match the word London exactly.
^London$
伦敦"或巴黎"
与伦敦或巴黎一词完全匹配的字符串.
Strings that match either the words London or Paris exactly.
^(London|Paris)$
不是伦敦"
除伦敦外的任何字符串.
Any string but London.
您匹配^London$并反转结果(NOT)
像"S *"一样
任何以字母s开头的字符串.
Any string beginning with the letter s.
^s
像"* st"一样
任何以字母st结尾的字符串.
Any string ending with the letters st.
st$
像"*狗*"一样
任何包含单词"the"和"dog"的字符串，在其之前，之间或末尾包含任何字符.
Any strings that contain the words 'the' and 'dog' with any characters before, in between, or at the end.
the.*dog
就像"[A-D] *"
Like "[A-D]*"
任何以字母A到D开头的字符串，其后都是其他字符串.
Any strings beginning with the letters A through D, followed by anything else.
^[A-D]
不像"*伦敦*"
任何在任何地方都不包含伦敦字的字符串.
Any strings that do not contain the word London anywhere.
反转London的匹配结果(您可以使用负向超前，例如:
^(.(?!London))*$，但我认为Access可以使用的更基本的Regex引擎不可用.
Reverse the matching result for London (you can use negative lookahead like:
^(.(?!London))*$, but I don't think it's available to the more basic Regex engine available to Access).
不喜欢"L *"
任何不以L开头的字符串.
Any strings that don't begin with an L.
^[^L]单个字符的否定匹配比上面整个单词的否定匹配容易.
^[^L] negative matching for single characters is easier than negative matching for a whole word as we've seen above.
像"L *"，而不像伦敦*"
Like "L*" And Not Like "London*"
任何以字母L开头但不是伦敦词的字符串.
Any strings that begin with the letter L but not the word London.
^L(?!ondon).*$
在SQL条件中使用正则表达式
在Access中，创建可直接在SQL查询中直接使用的用户定义函数很容易.
要在查询中使用正则表达式匹配，请将此函数放在模块中:
Using Regexes in SQL Criteria
In Access, creating a user-defined function that can be used directly in SQL queries is easy.
To use regex matching in your queries, place this function in a module:
' ----------------------------------------------------------------------'
' Return True if the given string value matches the given Regex pattern '
' ----------------------------------------------------------------------'
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 = True
.IgnoreCase = True
.MultiLine = True
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
然后，您可以在查询条件中使用它，例如在 PartTable 表中查找与screw 18mm的变体匹配的所有部分，例如Pan Head Screw length 18 mm甚至是SCREW18mm等.
Then you can use it in your query criteria, for instance to find in a PartTable table, all parts that are matching variations of screw 18mm like Pan Head Screw length 18 mm or even SCREW18mm etc.
SELECT PartNumber, Description
FROM PartTable
WHERE RegexMatch(Description, "screw.*?d+\s*mm")
注意事项
-
因为正则表达式匹配使用旧的脚本库，所以正则表达式语言的风格比.Net中其他编程语言可用的语言要受限制.
它仍然相当强大，因为它与JavaScript使用的功能大致相同.
阅读 VBScript正则表达式引擎，以检查可以做什么和不能做什么.Caveat
Because the regex matching uses old scripting libraries, the flavour of Regex language is a bit more limited than the one found in .Net available to other programming languages.
It's still fairly powerful as it is more or less the same as the one used by JavaScript.
Read about the VBScript regex engine to check what you can and cannot do.但是，更糟糕的是，使用此库进行的正则表达式匹配可能相当慢，因此您应该非常小心，不要过度使用它.
The worse though, is probably that the regex matching using this library is fairly slow and you should be very careful not to overuse it.
也就是说，有时它可能非常有用.例如，我使用正则表达式对来自用户的数据输入进行了清理，并检测了具有应被规范化的相似模式的条目.
经常使用的正则表达式可以增强数据一致性，但要谨慎使用.That said, it can be very useful sometimes. For instance, I used regexes to sanitize data input from users and detect entries with similar patterns that should have been normalised.
Well used, regexes can enhance data consistency, but use sparingly.这篇关于将基本Access查询条件表示为正则表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
