Regular Expressions in SQL Server servers?

By : bovium
Source: Stackoverflow.com
Question!

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

If not Microsoft really should consider that feature.

By : bovium


Answers

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).

By : Tomalak


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.

By : Junx


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



This video can help you solving your question :)
By: admin