SQL Server 服务器中的正则表达式？
是否可以使用完整的正则表达式功能集进行高效的查询。
如果不是的话，微软真的应该考虑这个功能。
Is it possible to make efficient queries that use the complete regular expression feature set.
If not Microsoft really should consider that feature.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
对于 SQL Server 2000（以及任何其他 32 位版本的 SQL Server），有 xp_pcre< /a>，它将 Perl 兼容的正则表达式引入为一组扩展存储过程。 我用过，有效。
较新的版本使您可以直接访问.NET 集成正则表达式 （此链接似乎已失效，这是另一个链接：MSDN：如何：使用 CLR 数据库对象）。
For SQL Server 2000 (and any other 32 bit edition of SQL Server), there is xp_pcre, which introduces Perl compatible regular expressions as a set of extended stored procedures. I've used it, it works.
The more recent versions give you direct access to the .NET integrated regular expressions (this link seems to be dead, here is another one: MSDN: How to: Work with CLR Database Objects).
答案是否定的，在一般情况下不是，尽管这可能取决于您所说的高效的含义。 出于这些目的，我将使用以下定义：“以合理的顺序有效地使用索引和连接”，这可能与任何定义一样好。
在这种情况下，“高效”查询是“s-arg”可用的，这意味着它们可以使用索引查找来缩小搜索谓词的范围。 等式（t 型连接）和简单的不等式可以做到这一点。 “AND”谓词也可以做到这一点。 之后，我们进入表、索引和范围扫描——即必须进行逐条记录（或逐个索引键）比较的操作。
Sontek 的答案描述了一种将正则表达式功能内联到查询中的方法，但操作仍然必须逐条记录地进行比较。 将其包装在函数中将允许基于函数的索引，其中计算结果在索引中具体化（Oracle 支持此功能，并且您可以通过使用讨论的技巧 本文）。 但是，您无法对任意正则表达式执行此操作。
在一般情况下，正则表达式的语义不适合以索引的方式修剪匹配集，因此将 rexegp 支持集成到查询优化器中可能是不可能的。
The answer is no, not in the general case, although it might depend on what you mean by efficient. For these purposes, I'll use the following definition: 'Makes effective use of indexes and joins in a sensible order' which is probably as good as any.
In this case, 'Efficient' queries are 's-arg'-able, which means that they can use index lookups to narrow down search predicates. Equalities (t-joins) and simple inequalities can do this. 'AND' predicates can also do this. After that, we get into table, index and range scanning - i.e. operations that have to do record-by-record (or index-keyby index-key) comparisons.
Sontek's answer describes a method of in-lining regexp functionality into a query, but the operations still have to do comparisons on a record by record basis. Wrapping it up in a function would allow a function-based index where the result of a calculation is materialised in the index (Oracle supports this and you can get equivalent functionality in SQL Server by using the sort of tricks discussed in this article). However, you could not do this for an arbitrary regexp.
In the general case, the semantics of a regular expression do not lend themselves to pruning match sets in the sort of way that an index does, so integrating rexegp support into the query optimiser is probably not possible.
查看此和此。 他们是关于如何做到这一点的很棒的帖子。
Check out this and this. They are great posts on how to do it.
我希望能够在 SQL Server 中本机调用正则表达式以进行即席查询并在存储过程中使用。 我们的 DBA 不允许我们创建 CLR 函数，所以我一直使用 LINQ Pad 作为一种可怜的东西男士用于临时内容的查询编辑器。 当处理保存到数据库的结构化数据（例如 JSON 或 XML）时，它特别有用。
我同意，没有正则表达式支持似乎是一种疏忽，这似乎是查询语言的一个明显特征。 希望我们能在未来的版本中看到它，但人们已经要求它很长时间了，但它还没有进入产品。
我看到反对它的最常见原因是，格式不正确的表达式可能会导致灾难性回溯 在 .NET 中不会中止，并且几乎总是需要重新启动计算机。 也许一旦他们在框架中解决了这个问题，我们就会看到它包含在 SQL Server 的未来版本中。
I would love to have the ability to natively call regular expressions in SQL Server for ad hoc queries and use in stored procedures. Our DBA's won't allow us to create CLR functions so I have been using LINQ Pad as a kind of poor man's query editor for the ad hoc stuff. It is especially useful when working with structured data such as JSON or XML that has been saved to the database.
And I agree that it seems like an oversight that there is no regular expression support, it seems like an obvious feature for a query language. Hopefully we will see it in a future version but people have been asking for it for a long time and it hasn't made it's way into the product yet.
The most frequent reason I have seen against it is that a poorly formed expression can cause catastrophic backtracking which in .NET will not abort and almost always requires the machine to be restarted. Maybe once they address that in the framework we will see it included in a future version of SQL Server.
我认为我们可以从 SQL Server 2008 中的新类型（hierarchyid、geo-spatial）中看到，如果 Microsoft 添加此类型，它将以 SQL CLR 程序集的形式出现。
如果您能够将程序集安装到数据库中，您可以滚动通过在 Visual Studio 中创建一个新的 Database\SQL Server 项目来创建您自己的项目 - 这将允许您创建新的触发器/UDF/存储过程/聚合或 UDT。 您可以将 System.Text.RegularExpressions 导入到类中并从那里开始。
希望这可以帮助
I think we can see from the new types in SQL Server 2008 (hierarchyid, geo-spatial) that if Microsoft do add this it will come in the form of a SQL CLR Assembly
If you are able to install Assemblies into your database you could roll your own by creating a new Database\SQL Server project in Visual Studio - this will allow you to make a new Trigger / UDF / Stored Proc / Aggregate or UDT. You could import System.Text.RegularExpressions into the class and go from there.
Hope this helps