将基本 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.
伦敦"或巴黎"
与 London 或 Paris 完全匹配的字符串.
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*"
Like "*the*dog*"
包含单词 'the' 和 'dog' 以及前面、中间或末尾的任何字符的任何字符串.
Any strings that contain the words 'the' and 'dog' with any characters before, in between, or at the end.
喜欢[A-D]*"
任何以字母 A 到 D 开头的字符串，后接其他任何字符串.
Any strings beginning with the letters A through D, followed by anything else.
不像*伦敦*"
任何地方都不包含 London 一词的任何字符串.
Any strings that do not contain the word London anywhere.
不像L*"
任何不以 L 开头的字符串.
Any strings that don't begin with an L.
喜欢L*"而不喜欢London*"
Like "L*" And Not Like "London*"
任何以字母 L 开头但不包含 London 的字符串.
Any strings that begin with the letter L but not the word London.
推荐答案
正则表达式比您在 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$
伦敦"或巴黎"
与 London 或 Paris 完全匹配的字符串.
Strings that match either the words London or Paris exactly.
^(London|Paris)$
不是伦敦"
除伦敦以外的任何字符串.
Any string but London.
您匹配 ^London$ 并反转结果 (NOT)
You match for ^London$ and invert the result (NOT)
喜欢S*"
任何以字母 s 开头的字符串.
Any string beginning with the letter s.
^s
喜欢*st"
任何以字母 st 结尾的字符串.
Any string ending with the letters st.
st$
喜欢*the*dog*"
Like "*the*dog*"
包含单词 '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]*"
以字母 A 到 D 开头的任何字符串，后接其他任何字符串.
Any strings beginning with the letters A through D, followed by anything else.
^[A-D]
不像*伦敦*"
任何地方都不包含 London 一词的任何字符串.
Any strings that do not contain the word London anywhere.
反转 London 的匹配结果(您可以使用 negative lookahead 之类的:^(.(?!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*"而不喜欢London*"
Like "L*" And Not Like "London*"
任何以字母 L 开头但不包含 London 的字符串.
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头螺钉长度18mm甚至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屋！
