REGEXP_MATCHES (Transact-SQL)

Applies to: SQL Server 2025 (17.x) Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

The REGEXP_MATCHES table-valued regular expression function returns a table of captured substrings that match a regular expression pattern to a string. If no match is found, the function returns no row.

REGEXP_MATCHES
(
    string_expression,
    pattern_expression [ , flags ]
)

Arguments

string_expression

An expression of a character string.

Can be a constant, variable, or column of character string.

Data types: char, nchar, varchar, or nvarchar.

Note

The REGEXP_* functions support LOB types (varchar(max) and nvarchar(max)) up to 2 MB for the string_expression parameter.

pattern_expression

Regular expression pattern to match. Usually a text literal.

Data types: char, nchar, varchar, or nvarchar. pattern_expression supports a maximum character length of 8,000 bytes.

flags

One or more characters that specify the modifiers used for searching for matches. Type is varchar or char, with a maximum of 30 characters.

For example, ims. The default is c. If an empty string (' ') is provided, it will be treated as the default value ('c'). Supply c or any other character expressions. If flag contains multiple contradictory characters, then SQL Server uses the last character.

For example, if you specify ic the regex returns case-sensitive matching.

If the value contains a character other than those listed at Supported flag values, the query returns an error like the following example:

Invalid flag provided. '<invalid character>' are not valid flags. Only {c,i,s,m} flags are valid.

Supported flag values

Flag	Description
i	Case-insensitive (default false)
m	Multi-line mode: ^ and $ match begin/end line in addition to begin/end text (default false)
s	Let . match \n (default false)
c	Case-sensitive (default true)

Returns

Returns a tabular result as follows:

Column name	Data type	Description
match_id	bigint	The sequence of matching words.
start_position	int	Starting index position.
end_position	int	Ending index position.
match_value	Same type as string_expression or varchar	Matching expression.
substring_matches	json	JSON document describing match.

Platform support

Requires database compatibility level 170. To set database compatibility level, review ALTER DATABASE (Transact-SQL) compatibility level. The compatibility level requirement doesn't apply if the ALLOW_BUILTIN_TVF_IN_ALL_COMPAT_LEVELS database-scoped configuration is enabled.

• In Azure SQL Managed Instance, T-SQL regular expression functions are available with the SQL Server 2025 or Always-up-to-date update policy only.
• T-SQL regular expression functions are available in Azure SQL Database, SQL database in Fabric, and starting in SQL Server 2025.
• T-SQL regular expression functions aren't available in SQL Server 2022 or earlier versions.
• In Fabric Data Warehouse, the regular expression table-valued functions REGEXP_MATCHES and REGEXP_SPLIT_TO_TABLE aren't supported. T-SQL regular expression scalar functions are available as a preview feature in Fabric Data Warehouse.

Examples

Return tabular results from 'Learning #AzureSQL #AzureSQLDB' that start with a # character followed by one or more alphanumeric characters (A-Z, a-z, 0-9) or underscores (_).

SELECT *
FROM REGEXP_MATCHES ('Learning #AzureSQL #AzureSQLDB', '#([A-Za-z0-9_]+)');

match_id start_position end_position match_value substring_matches
1        10             18           #AzureSQL   [{"value":"AzureSQL","start":11,"length":8}]
2        20             30           #AzureSQLDB [{"value":"AzureSQLDB","start":21,"length":10}]

Return strings from ABC that match strings that start with the letter A followed by exactly two characters.

SELECT *
FROM REGEXP_MATCHES ('ABC', '^(A)(..)$');

match_id start_position end_position match_value substring_matches
1        1              3            ABC         [{"value":"A","start":1,"length":1},{"value":"BC","start":2,"length":2}]

Related content

• Regular expressions functions