Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Regex functionality in pattern matching

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.

3 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    unplanned  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Upvotes: 419

    <=-=Mar 8 2007 3:20AM=-=>

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

    <=-=Mar 21 2007 1:23PM=-=>

    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?

    <=-=Aug 20 2007 10:21PM=-=>

    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

    <=-=Aug 24 2007 6:21AM=-=>

    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.

    <=-=Dec 27 2007 4:42PM=-=>

    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.

    <=-=Aug 13 2008 11:29AM=-=>

    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.

    <=-=Nov 2 2008 4:05PM=-=>

    I’d like to see regular expressions with the substitution options as well.

    <=-=May 27 2009 3:15PM=-=>

    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(\d{7})’,AreaCode,LocalNumber) FROM phones

    Result set:

    AreaCode,LocalNumber
    206 5551212

    <=-=Jun 8 2009 3:43PM=-=>

    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.

    <=-=Jun 20 2011 4:06PM=-=>

    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.

    <=-=Jun 21 2011 5:07PM=-=>

    Yes, please add regular expressions to SQL Server!

    <=-=Oct 5 2011 10:48AM=-=>

    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/

    <=-=Jan 9 2012 12:19PM=-=>

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

    <=-=Oct 11 2012 5:39AM=-=>

    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.

    <=-=Jun 14 2013 8:11AM=-=>

    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

    <=-=Sep 6 2013 2:17PM=-=>

    Folks – this has been slated for a “future” release for over 6 years. Any idea when we’ll see this?

    <=-=Dec 5 2013 10:31AM=-=>

    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.

    <=-=Apr 23 2016 9:04AM=-=>

    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.

    <=-=Nov 28 2016 3:55AM=-=>

    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 …

    <=-=Dec 27 2016 4:58AM=-=>

    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.

    <=-=Sep 27 2017 1:33AM=-=>

    10 years later and we still don’t have native REGEX in SQL server.
    Look at how Oracle/Others are doing it if it is too complicated for you (MS).

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Thomas Trias commented  ·   ·  Flag as inappropriate

        I agree that a new predicate is the best way to go, much like Oracle's RLIKE or SyBase's REGEXP (which have been around for quite some time). While we can currently get "similar" functionality using .NET extensions, those will not take advantage of indexes when using left-anchored regular expressions.

      Feedback and Knowledge Base