Regex functionality in pattern matching - by Simon Sabin

Status : 

 


508
2
Sign in
to vote
ID 261342 Comments
Status Active Workarounds
Type Suggestion Repros 9
Opened 3/3/2007 4:21:14 PM
Duplicates 296631 361357 474722 Access Restriction Public

Description

It would be good to be able to have full regex capabilities when using LIKE or Patindex.

It would also be good to have a regex table function that returned matches.
Sign in to post a comment.
Posted by Reader Man Me on 12/27/2016 at 4:58 AM
Maybe this might give an incentive:
http://eval-sql.net
maybe by adding a new SQL keyword 'regex', will help in not affecting the 'like' keyword, and then studding the different implementation ways and their performance/feasibility.
Posted by cdickey on 11/28/2016 at 3:55 AM
I would like to see built in REGEX capabilities. It is about time.
As people have stated, using CLR can be problematic for a lot of reasons.
With LIKE you often end up doing multiple passes through large text columns when AND and OR are needed to match multiple criteria.
A single pass compiled regular expression search looking for multiple criteria would certainly be more efficient when processing lots of text data.
It certainly would be useful to be able to search stored proc T-SQL with regular expressions. Where does that column in that table get updated?
COMPRESS() finally showed up. Next ...
Posted by scott_m on 4/23/2016 at 9:04 AM
We added regex functionality via SQL CLR proc. See https://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/

It works well. However, ever since we started used it our sql error log is flooded with this message:

AppDomain 248 (master.sys[runtime].261) is marked for unload due to memory pressure.
Posted by Bono_Operatur on 12/5/2013 at 10:31 AM
Please add full regular expressions to SQL Server. I know that's possible after installing MDS, but MDS isn't an option at my company.
Posted by Mjsexton1 on 9/6/2013 at 2:17 PM
Folks - this has been slated for a "future" release for over 6 years. Any idea when we'll see this?
Posted by kalwin on 6/14/2013 at 8:11 AM
Posted by Microsoft on 8/20/2007 at 10:21 PM

We are definitely going to consider adding this capability in the query language in a future release.
Vineet Rao Program Manager Microsoft SQL Server
-----------------------------------------------------------------------------

It looks that MS definitely considered and rejected the idea of adding RegEx. How many releases did we have since 2007?

Peter Krolikowski
Posted by Wim SQL Server on 10/11/2012 at 5:39 AM
You can add Reged capabilities by using a CLR but indeed would be better if it was included directly so this security feature does not have to be enabled.
Posted by Garrett Fitzgerald on 1/9/2012 at 12:19 PM
I use RegEx functionality in Oracle to join a table with a field that contains a comma-separated list of roles that a user belongs to with the table containing the role definitions. Granted, this rarely comes up for me outside of this particular context, but it sure was handy to be able to do. :-)
Posted by SQLWork on 10/5/2011 at 10:48 AM
Then there is this review from Simple-Talk: http://www.simple-talk.com/sql/sql-tools/sql-(sqlsharp)-a-review/
Product Site: (Free and Pay Versions) http://www.sqlsharp.com/download/
Posted by Cathan Cook on 6/21/2011 at 5:07 PM
Yes, please add regular expressions to SQL Server!
Posted by Oliver Wilcock on 6/20/2011 at 4:06 PM
Echo, echo.
Let there be built in regular expressions in SQL Server.
Let there be regex predicates (standard regex).
Let there also be ANSI standard similar
Let there be regex based search and replace functions.
I haven't used Oracle, but I'm quite happy with the implementation in MySQL.
Failing that let there be downloadable CLR binaries from Microsoft.
Posted by RogueCoder on 6/8/2009 at 3:43 PM
Using CLR in a high security environment means quite a bit of paperwork, although it is possible. I prefer the idea of adding the RegEx functionality to the core language. It seems to me that RegEx support could be added to the existing REPLACE, PATINDEX, LIKE operators using an additional and optional parameter that would change the way the statements operated.
Posted by Peter3412 on 5/27/2009 at 3:15 PM
In addition to standard RegEx search/replace some way of doing named or anon captures would IMHO be extremely useful.

Something along the lines of:

SELECT RECAPTURE( PhoneNumber,'(\d{3})(\d{7})',AreaCode,LocalNumber) FROM phones

Result set:

AreaCode,LocalNumber
206         5551212
Posted by DMulvena on 11/2/2008 at 4:05 PM
I'd like to see regular expressions with the substitution options as well.
Posted by Erland Sommarskog on 8/13/2008 at 11:29 AM
LIKE and PATINDEX should stay where they are for ANSI compliance and backwards compatibility.

SIMILAR TO could be implemented for SQL standards compatibility, but the ANSI "Regular expressions"
are very non-standard and different from what is in Unix and .Net.

The name of the operator is irrelevant, the important point is that what we get is something that is
familiar to people who have already worked with regular expression. Beside a new LIKE and a new
PATINDEX, a new REPLACE would also be needed.
Posted by Jason Kresowaty on 12/27/2007 at 4:42 PM
The CLR integration is not very friendly to ad hoc use of regular expressions. Both find and replace operators in SQL that take regular expressions would be very useful (preferably with a regex syntax identical to that used by .NET).

Another advantage is that regular expressions can probably can be more efficiently accomplished as part of the SQL engine than in SQL, since the SQL engine could compile the regex prior to execution of the query.

CLR does have regex compilation, but if I recall correctly, a compiled expression is never discarded from memory. Thus is is not really the correct design for ad hoc regular expressions.
Posted by Stan Segers on 8/24/2007 at 6:21 AM
Every time customers ask for RegEx in SQL, MS employees seem consistent in their answers: go .NET CLR
However, .NET CLR is disabled by default and there are environments where it doesn't get enabled, period. It's in those environments we need the power of RegEx directly in SQL Server.
Posted by Microsoft on 8/20/2007 at 10:21 PM
Hello,

First of all i would like to thank all of you for sending your feedback to us. We are definitely going to consider adding this capability in the query language in a future release. However, until then have you considered using CLR integration for this purpose. .NET has the capability and it is very simple to use .NET to add this capability by adding a user-defined function that does this. The blog entry https://blogs.msdn.com/sqlclr/archive/2005/06/29/regex.aspx has sample code and discusses the available options via .NET.

Thanks,
Vineet Rao
Program Manager
Microsoft SQL Server
Posted by AaronBertrand on 3/21/2007 at 1:23 PM
I echo the wish to not overload LIKE or PATINDEX. Personally I don't like SIMILAR either, though. How about a REGEX function with REGEX somewhere in the name?
Posted by Mark Yudkin on 3/8/2007 at 3:20 AM
I agree with the functionality request, but not the suggested approach of enhancing the LIKE, PATINDEX or adding a table function.

This functionality should be implemented by supporting the SQL99 SIMILAR predicate (ISO 9075 feature T141, ISO 9075-2-1999 section 8.6).