posted on Thursday, May 19, 2005 8:41 AM
by
amachanic
Regular Expressions in SQL Server
Can you use regular expressions in a WHERE clause?
Ken Henderson shows us how, in this recent blog post on the topic.
Given that this is a question that comes up very commonly in forums, I thought I should put a pointer here for anyone who missed the post and requires this functionality.
Along with this pointer, I'd like to gently remind readers of what Ken left out of the article: For larger tables, this technique will perform very poorly! There are two reasons for this. One, an index on the column being searched cannot be used to help satisfy a query if that column is being searched using a function. This is due to the fact that the column itself is indexed, not the output of the function as it applies to the column. SQL Server has no way to know how the function will return before actually calling it. The second problem is that there is overhead associated with invoking a COM object. Although this overhead is not huge when dealing with only a few rows, it will add up when querying a large table -- especially because every row in the table may need to be queried due to the fact that an index can't be used!
So while you should certianly add this to your emergency box of tricks, make sure to use it with caution and test carefully.
... And coming soon to a blog reader near you, a SQLCLR version of this technique -- I think this will be a good opportunity to performance test COM automation vs. CLR functions. Stay tuned...