SQL Server 2025 REGEXP_MATCHES Function to Extract Multiple Substrings

Problem

We have a use case where we need to extract a substring of a text. In addition, it needs to match a regular expression (regex). It’s also possible there are multiple matches in one piece of text. How can we efficiently extract those substrings without using a loop or a cursor?

Solution

In the SQL Server 2025, support for functions that can handle regular expressions have been added to T-SQL. This is a long-awaited feature. You can find an overview in the tip New SQL Regex Functions in SQL Server 2025 and SSMS. At the time of writing, SQL Server 2025 was in preview. It is possible functionality might’ve been changed in the final release. If you don’t have SQL Server 2025, test the new functions in Azure SQL DB or Fabric SQL DB.

In this tip, we’ll take a closer look at the REGEXP_MATCHES function. This function is able to return multiple rows if more than one match with the regex pattern has been found.

REGEXP_MATCHES Function Syntax

The following syntax must be used for the new function:

--MSSQLTips.com
REGEXP_MATCHES(string expression, regular expression, [flags])

The first two parameters are mandatory:

  • string expression, which is the text (this can also be a column) in which you want to search
  • regular expression, which is the pattern you want to match

There’s one optional parameter, flags, and its options are explained in the tip REGEXP_LIKE Function in SQL Server 2025 (in the section “The Optional Flag Parameter”). There are other new T-SQL functions in SQL Server 2025 that allow you to extract text using a regex pattern, such as REGEXP_SUBSTR for example. The biggest difference – aside from REGEXP_MATCHES having for less parameters than some of the other functions – is that REGEXP_MATCHES is a table-valued function. This means that REGEXP_MATCHES is used in the FROM clause of a SQL statement, instead of in the SELECT (or other clauses like WHERE or GROUP BY) and its result is a table. Let’s show the difference between the two with an example. The following queries extract a credit card number from a piece of text:

--MSSQLTips.com
SELECT REGEXP_SUBSTR('Hello, 1234 5678 1234 5678 is my credit card number.','(\d{4}) (\d{4}) (\d{4}) (\d{4})');
 
SELECT *
FROM REGEXP_MATCHES('Hello, 1234 5678 1234 5678 is my credit card number.','(\d{4}) (\d{4}) (\d{4}) (\d{4})')
regex substring extraction using REGEXP_SUBSTR and REGEXP_MATCHES

As you can see, the first function returns a scalar value, while REGEXP_MATCHES returns a table with the following columns:

  • match_id: the sequence of matches found
  • start_position: the index of the first character of the match (1-based, meaning the first character gets position 1)
  • end_position: the index of the last character of the match (1-based)
  • match_value: the expression found that matches the regex pattern
  • substring_matches: a JSON document that lists the match or different matching subgroups (more on this later)

Multiple Matches with REGEXP_MATCHES

The strength of the REGEXP_MATCHES function is that it can return multiple rows (since it’s a table-valued function) if there are multiple matches. Other T-SQL regex functions need some sort of looping to extract multiple matches, as demonstrated in the tips Extract Text using Regular Expressions with SQL Server 2025 Function REGEXP_INSTR and Powerful Text Extraction Using New REGEXP_SUBSTR Function in SQL Server 2025. REGEXP_MATCHES can handle this with one function call. Let’s demonstrate this using the restaurant review sample data (as shown in the tip SQL Server 2025 REGEXP_COUNT Function to Count Occurrences in Text).

First, we’re going to group review data together, so we have multiple zip codes in one row:

--MSSQLTips.com
WITH cte_src AS
(
    SELECT
         grp = ReviewID % 10
        ,ReviewText
    FROM dbo.Reviews
)
, cte_longreviews AS
(
    SELECT
         grp
        ,ReviewText_Long = STRING_AGG(ReviewText,' ')
    FROM cte_src
    GROUP BY grp
)
SELECT *
INTO #longreviews
FROM cte_longreviews;

We can now extract the zip codes using APPLY:

--MSSQLTips.com
SELECT *
FROM #longreviews l
CROSS APPLY REGEXP_MATCHES(l.ReviewText_Long,'\d{5}([-]|\s*)?(\d{4})?') r
multiple matches per row with REGEXP_MATCHES

It’s easy to see that REGEXP_MATCHES drastically reduces code complexity. It also seems to be a bit faster than the solution with REGEXP_SUBSTR. When we blow up the review tables to 10,000 records, we get the following execution plans:

execution plans of REGEXP_SUBSTR and REGEXP_MATCHES

Asides from returning the extracted text, it also provides you with the start and end positions of each match which is the exact functionality of the REGEXP_INSTR function.

What about Subgroups?

Some of the regex functions support subgroups, where a fragment of a matched pattern is returned. In the credit card example, there are 4 subgroups and it’s possible to return the last 4 digits only as demonstrated in the REGEXP_SUBSTR tip. REGEXP_MATCHES doesn’t have a parameter for groups, but the substr_matches column does have information about possible matching subgroups. Using OPENJSON and JSON_VALUE we can extract the different subgroups:

--MSSQLTips.com
DECLARE @text_to_search VARCHAR(100) = 'Hello, 1234 5678 1234 5678 is my credit card number.';
SELECT
     r.substring_matches
    ,j.[key]
    ,subgroup_info = j.[value]
    ,subgroup_data = JSON_VALUE(j.[value],'$.value')
FROM REGEXP_MATCHES(@text_to_search,'(\d{4}) (\d{4}) (\d{4}) (\d{4})') r
CROSS APPLY OPENJSON(r.substring_matches) j;
extracting subgroups using JSON functions

If you’d be interested in the last 4 characters of a credit card number, you can filter on key = 3 (this time the counting is 0-based).

Conclusion

The new REGEXP_MATCHES can be used to extract text using regular expressions. It’s an interesting function as it is table-valued and it can handle multiple matches at once. REGEXP_MATCHES basically combines the functionality of REGEXP_SUBSTR and REGEXP_INSTR in one single function call. It also supports subgroups, but this requires extra JSON parsing. Once you know you might have multiple matches for your regex pattern in a single piece of text, the REGEXP_MATCHES is the recommended function to use.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *