Should SQL Support Regular Expressions?
One of my smaller goals for this year is to become more proficient using Regular Expressions. Spending a majority of the last two decades in pure SQL allowed me to miss this gem. As I use Regular Expressions more and more, I have come to appreciate the elegance of the utility, and wonder how it might be used in conjunction with SQL?
First let me say that I’m a strong proponent of performing as much business logic as possible outside my SQL Engine as possible. Yet, there are many times when it would seem useful to have Regular Expressions available. When it is easier on my database server to perform filtering data than to return data to an external filter, using Regular Expressions in a database could make sense.
My first thought would be to use Regular Expressions as a filtering tool in a where clause. Obviously, this is not going to perform well on large tables. This is due to the fact it would not be able to utilize an Index for the filter evaluation. Still, this is often the case when we have to use operators such as LIKE with wild cards at the beginning of its comparison.
Date Comparisons are always a pain. Consider Data stored in DateTime or SmallDateTime fields. When you wish to query these fields when either date or time is not relevant you have to jump through a bunch of hoops. You must either extract DATEPART, manipulate the date removing the unwanted attribute, or some other form of manipulation. In this case, an index also may not be of benefit. Even here Regular Expressions would be a much more efficient technique for date comparisons in that it can natively ignore the unwanted attribute.
One of the often missed capabilities of Regular Expressions is the ability to identify matches to a pattern, and replace those matches with different text. This capability is very powerful. In fact, it can even be nested or use anonymous methods producing even more power.
Regular expressions are not natively supported in many of the relational data engines I have used. However, using the SQL Server CLR you could write your own user defined functions taking advantage of Regular Expressions in that they are supported readily in Dot Net.
Am I missing something here? This seems like a no brainer here. I could readily extend an SQL Server engine by writing a CLR Regular Expression function. I’m sure it could be done in Oracle just as handily. I think it would be an interesting addition to the ANSI SQL specifications and a great addition to other relational engines in a native form.
What do you think? Is this something that makes sense to you? Send your comments to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Using System Monitor to Tune SQL Server 2008
In this article, you can find some helpful tips to use System Monitor with SQL Server 2008.
Featured White Paper(s)
Structuring the Unstructured: How to Dimensionalize Semi-Structured Business Data
Written by Interactive Edge
The Business Intelligence industry … (read more)