blocks|key|1697158|text|查看this和this。它们是关于如何做到这一点的很好的帖子。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1697159|entityMap|0|LINK|mutability|MUTABLE|url|http://blogs.msdn.com/khen1234/archive/2005/05/11/416392.aspx|1|http://www.sqlteam.com/article/regular-expressions-in-t-sql^0|2|4|0|7|4|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

Check out <a href="http://blogs.msdn.com/khen1234/archive/2005/05/11/416392.aspx" rel="nofollow noreferrer">this</a> and <a href="http://www.sqlteam.com/article/regular-expressions-in-t-sql" rel="nofollow noreferrer">this</a>. They are great posts on how to do it.

blocks|key|1482037|text|对于SQL+Server2000(以及任何其他32位版本的SQL+Server2000)，都有xp_pcre，它引入了与Perl兼容的正则表达式作为一组扩展的存储过程。我用过了，很管用。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1482038|最近的版本让你可以直接访问.NET+integrated+regular+expressions+(这个链接似乎已经死了，这里还有一个：MSDN:+How+to:+Work+with+CLR+Database+Objects。|1482039|entityMap|0|LINK|mutability|MUTABLE|url|http://www.codeproject.com/KB/database/xp_pcre.aspx|1|http://msdn.microsoft.com/en-us/magazine/cc163473.aspx|2|https://msdn.microsoft.com/en-us/library/hh272683(v=vs.103%2529.aspx)^0|1B|7|0|0|D|Z|1|1W|18|2|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]]|C|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@$A|W|B|X|1|Y]|$A|Z|B|10|1|11]]|C|$]]|$1|F|3|-4|5|6|7|12|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]|P|$5|I|J|K|C|$L|Q]]]]

For SQL Server 2000 (and any other 32 bit edition of SQL Server), there is <a href="http://www.codeproject.com/KB/database/xp_pcre.aspx" rel="nofollow noreferrer">xp_pcre</a>, 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 <a href="http://msdn.microsoft.com/en-us/magazine/cc163473.aspx" rel="nofollow noreferrer">.NET integrated regular expressions</a> (this link seems to be dead, here is another one: <a href="https://msdn.microsoft.com/en-us/library/hh272683(v=vs.103).aspx" rel="nofollow noreferrer">MSDN: How to: Work with CLR Database Objects</a>).

blocks|key|1482104|text|答案是否定的，在一般情况下不是，尽管这可能取决于你所说的效率。出于这些目的，我将使用以下定义：“以合理的顺序有效地使用索引和连接”，这可能是最好的定义。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1482105|在这种情况下，“高效”查询是“s-arg”-able的，这意味着它们可以使用索引查找来缩小搜索谓词的范围。等式(t-join)和简单的不等式可以做到这一点。‘'AND’谓词也可以做到这一点。之后，我们进入表、索引和范围扫描-即必须逐条记录(或索引-键-索引-键)进行比较的操作。|1482106|Sontek的回答描述了一种将regexp功能内联到查询中的方法，但操作仍然必须逐条记录地进行比较。将其包装在函数中将允许基于函数的索引，其中计算结果在索引中具体化(Oracle支持这一点，您可以通过使用讨论的in+this+article技巧在SQL+Server中获得等效功能)。但是，您不能对任意regexp执行此操作。|offset|length|1482107|在一般情况下，正则表达式的语义不像索引那样适合修剪匹配集，因此将rexegp支持集成到查询优化器中可能是不可能的。|1482108|entityMap|0|LINK|mutability|MUTABLE|url|http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3030008&SiteID=17^0|0|0|2X|F|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@$F|U|G|V|1|W]]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]]]

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 <a href="http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3030008&amp;SiteID=17" rel="noreferrer">in this article</a>). 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.

blocks|key|1482180|text|我认为我们可以从SQL+Server+2008中的新类型(hierarchyid、geo-spatial)中看到，如果Microsoft真的添加了它，它将以SQL+CLR程序集的形式出现|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1482181|如果您能够将程序集安装到您的数据库中，您可以通过在Visual+Studio中创建一个新的Database\SQL+Server项目来滚动自己的程序集-这将允许您创建一个新的触发器/+UDF+/存储的Proc+/+Aggregate或UDT。您可以将System.Text.RegularExpressions导入到类中，然后从那里开始。|1482182|希望这能有所帮助|1482183|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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

blocks|key|1482262|text|我希望能够在SQL+Server中本地调用正则表达式进行即席查询，并在存储过程中使用。我们的数据库管理员不允许我们创建CLR函数，所以我一直在使用LINQ+Pad作为一种特别的查询编辑器。在处理已保存到数据库的结构化数据(如JSON或XML+)时，它特别有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1482263|我同意这似乎是一个疏忽，没有正则表达式支持，这似乎是查询语言的一个明显特征。希望我们能在未来的版本中看到它，但人们已经要求它很长一段时间了，它还没有进入产品中。|1482264|我看到的反对它的最常见的原因是，格式不正确的表达式可能会导致catastrophic+backtracking，在.NET中它不会中止，并且几乎总是需要重新启动机器。也许一旦他们在框架中解决了这个问题，我们就会看到它包含在SQL+Server的未来版本中。|1482265|entityMap|0|LINK|mutability|MUTABLE|url|http://www.linqpad.net/|1|http://www.regular-expressions.info/catastrophic.html^0|21|8|0|0|0|U|P|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]]|C|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|H|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]|P|$5|K|L|M|C|$N|Q]]]]

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 <a href="http://www.linqpad.net/" rel="nofollow noreferrer">LINQ Pad</a> 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 <a href="http://www.regular-expressions.info/catastrophic.html" rel="nofollow noreferrer">catastrophic backtracking</a> 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.

Is it possible to make efficient queries that use the complete regular expression feature set.

If not Microsoft really should consider that feature.

Regular Expressions in SQL Server servers?

数据库管理

结构化数据

Visual Studio

SQL Server

数据库

JSON

Perl

是否可以使用完整的正则表达式功能集进行有效的查询。如果不是这样，微软真的应该考虑一下这个特性。

问SQL Server服务器中的正则表达式？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server服务器中的正则表达式？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server服务器中的正则表达式？
EN