Support POSIX-style regular expressions in T-SQL - by Erland Sommarskog

Status : 


Sign in
to vote
ID 361357 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 8/11/2008 1:54:24 PM
Access Restriction Public
Primary Feedback Item 261342


Regular expressions is a very powerful way to match data, far more powerful
than the LIKE operator in SQL. Regular expressions comes in a number of flavours:
POSIX defines one standard, .Net have their own twist (I think), and Perl is 
probably the most elaborate of them all. But they all share some common
traits: . is a wildchard charcted, * means previous pattern zero or more times etc.

ANSI/ISO have tried to catch up, and SQL:1999 introduced the SIMILAR TO
operator, but it's obvious that they are on the wrong track. They still use % and
_ as wildcard which is simply akward and non-standard. They also insist on
the string matching the pattern entirely which also is non-standard.

Today you can use regpexps through the CLR routines, but this is common 
enough to warrant a place directly in T-SQL. Also, that makes it difficult 
to use indexes with regexps when you do an anchored search.
Sign in to post a comment.
Posted by Microsoft on 8/13/2008 at 7:16 AM
Hi Erland,

I've resolved this as a duplicate of Connect #261342 and made sure your comments are reflected in that issue. I can tell you that this is under serious consideration right now. If you haven't already voted on that item, it would be worth it to do so.

Posted by Erland Sommarskog on 8/12/2008 at 2:12 PM
Of course, there should also a be a replace function that can work with regular expression. Both for the pattern to find - and for the replacement string.