Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server 2025 (17.x)
Azure SQL Database
Azure SQL Managed Instance
SQL analytics endpoint in Microsoft Fabric and Warehouse in Microsoft Fabric
SQL database in Microsoft Fabric
The REGEXP_COUNT scalar regular expression function counts the number of times that a regular expression pattern is matched in a string.
REGEXP_COUNT
(
string_expression,
pattern_expression [ , start [ , 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.
start
Specify the starting position for the search within the search string. Optional. Type is int or bigint.
The numbering is 1-based, meaning the first character in the expression is 1 and the value must be >= 1. If the start expression is less than 1, the returned pattern_expression begins at the first character that is specified in string_expression. If the start expression is greater than the length of string_expression, the function returns 0. The default is 1.
If the start expression is less than 1, the query returns an error.
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) |
Return value
int
Platform support
- 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.
- T-SQL regular expression scalar functions are available as a preview feature in Fabric Data Warehouse. For more information, see Regular expressions functions in Fabric Data Warehouse. T-SQL regular expression table-valued functions are not available in Fabric Data Warehouse.
Examples
Count how many times the letter a appears in each product name.
SELECT PRODUCT_NAME,
REGEXP_COUNT(PRODUCT_NAME, 'a') AS A_COUNT
FROM PRODUCTS;
Count how many products have a name that ends with ing.
SELECT COUNT(*)
FROM PRODUCTS
WHERE REGEXP_COUNT(PRODUCT_NAME, 'ing$') > 0;
Count how many products have a name that contains three consecutive consonants, ignoring case.
SELECT COUNT(*)
FROM PRODUCTS
WHERE REGEXP_COUNT(PRODUCT_NAME, '[^aeiou]{3}', 1, 'i') > 0;