REGEXP_COUNT in SQL Server 2025: Counting Pattern Matches in Text

REGEXP_COUNT returns how many times a pattern matches inside a piece of text. It arrived in SQL Server 2025. Until now, counting words, digits or separators meant string tricks that broke on edge cases. Let’s count with a pattern instead.

What the Function Does

The syntax is short: REGEXP_COUNT(text, pattern, start, flags). Only the first two arguments are required. Start is the position where counting begins, and flags change how the pattern behaves. The result is an int.

A pattern is a small description of text. For example, \w+ means a run of letters, digits or underscores, and \d means one digit. I ran every query here on SQL Server 2025. The function needed no preview setting: PREVIEW_FEATURES stayed OFF in my test database.

SELECT REGEXP_COUNT(N'one two  three', N'\w+') AS words,
       REGEXP_COUNT(N'a1b22c333', N'\d') AS digit_chars,
       REGEXP_COUNT(N'a1b22c333', N'\d+') AS numbers,
       REGEXP_COUNT(N'aaaa', N'aa') AS no_overlap;

The first string holds three words, even with a double space between two of them. The second holds six digits in three separate numbers. \d counts every digit, while \d+ counts every run of digits. Most of the time you want the run.

The fourth column shows a rule worth knowing: matches never overlap. The pattern aa fits inside aaaa at three positions, but the count is 2. The second match can only start after the first one ends.

Build a Small Test

The sample table is customer feedback with an email, a list of tags and a comment. It carries the usual dirt: a doubled @, a doubled space, a NULL email and a NULL comment. The script can run twice.

IF DB_ID(N'SqlRegexpCountDemo') IS NULL CREATE DATABASE SqlRegexpCountDemo;
GO
USE SqlRegexpCountDemo;
GO
DROP TABLE IF EXISTS dbo.Feedback;
CREATE TABLE dbo.Feedback
(
    FeedbackID int IDENTITY(1,1) PRIMARY KEY,
    Email nvarchar(100) NULL,
    Tags nvarchar(100) NULL,
    Comment nvarchar(400) NULL
);
INSERT INTO dbo.Feedback (Email, Tags, Comment) VALUES
(N'asha.rao@example.com', N'wrap;delivery', N'Loved the paneer wrap. Delivery took 25 minutes.'),
(N'ben@@example.com', N'soup;bread;temperature', N'Great soup,  but the bread was cold.'),
(N'chloe.park@example.com', N'', N'Order 4521 had 3 items missing and 2 were refunded.'),
(N'dev@example.com@shop.com', N'salad', N'Sooo fresh!!! Loooved the salad!!!'),
(NULL, NULL, N'Please add a mango lassi to the menu.'),
(N'farah@example.com', N'dessert;sweet', NULL);

Start Position and Flags

Case matters by default. In my test, Banana holds three lowercase a and no uppercase A, even though the database collation ignores case. The i flag switches case matching off. A start of 3 skips the first two letters, so the count of a drops to two.

SELECT REGEXP_COUNT(N'Banana', N'a') AS lower_a,
       REGEXP_COUNT(N'Banana', N'A') AS upper_a,
       REGEXP_COUNT(N'Banana', N'A', 1, 'i') AS ignore_case,
       REGEXP_COUNT(N'Banana', N'a', 3) AS from_position_3;

Four flags exist: c for case-sensitive, i for case-insensitive, m for multi-line and s for dot-matches-newline. With m, the anchors ^ and $ match at every line break. With s, a dot also matches a line break. The next query uses a text with one line break in it.

SELECT REGEXP_COUNT(N'a' + CHAR(10) + N'b', N'^\w$') AS no_flag,
       REGEXP_COUNT(N'a' + CHAR(10) + N'b', N'^\w$', 1, 'm') AS m_flag,
       REGEXP_COUNT(N'a' + CHAR(10) + N'b', N'a.b', 1, 's') AS s_flag;

NULL, Empty Text and Errors

A NULL text or a NULL pattern returns NULL. Empty text returns 0. An empty pattern matches between every character, so abc returns 4.

SELECT REGEXP_COUNT(NULL, N'a') AS null_text,
       REGEXP_COUNT(N'abc', NULL) AS null_pattern,
       REGEXP_COUNT(N'', N'a') AS empty_text,
       REGEXP_COUNT(N'abc', N'') AS empty_pattern;

Bad input stops with an error, not with a quiet zero. These four calls each fail, and in each one the flaw is easy to miss.

SELECT REGEXP_COUNT(N'abc', N'a', 1, N'i');
GO
SELECT REGEXP_COUNT(N'abc', N'a', 0);
GO
SELECT REGEXP_COUNT(N'abc', N'[');
GO
SELECT REGEXP_COUNT(N'committee', N'(.)\1');

Msg 8116, Level 16, State 1, Line 1
Argument data type nvarchar is invalid for argument 4 of regexp_count function.
Msg 19301, Level 16, State 1, Line 1
'START' value should be greater than or equal to 1 but '0' is provided in 'REGEXP_COUNT' function.
Msg 19308, Level 16, State 2, Line 1
Missing ']' in the Pattern [.
Msg 19300, Level 16, State 1, Line 1
An invalid Pattern '(.)\1' was provided. Error 'invalid escape sequence: \1' occurred during evaluation of the Pattern.

The flags must be a varchar literal, so N’i’ fails and ‘i’ works. The start must be 1 or higher. The last error matters most. A backreference such as \1, which means “the same character again”, isn’t supported. To find stretched letters, list the letters instead, as the next query does with a{3,} (three or more a).

Counting in a Table

Now the real table. Each count answers one small question about the comment. How many words does it have? How many numbers? How many exclamation marks? How many stretched letters? I wrote the word pattern as [A-Za-z]+ on purpose. The shorter \w+ also counts digits as words, so row 3 would give ten words, not seven.

SELECT FeedbackID,
       REGEXP_COUNT(Comment, N'[A-Za-z]+') AS words,
       REGEXP_COUNT(Comment, N'\d+') AS numbers,
       REGEXP_COUNT(Comment, N'!') AS bangs,
       REGEXP_COUNT(Comment, N'(a{3,}|e{3,}|i{3,}|o{3,}|u{3,})') AS stretched
FROM dbo.Feedback;

Row 4 shows both habits of an excited customer: six exclamation marks and two stretched words, Sooo and Loooved. Row 6 has no comment, so every count is NULL, not 0. Keep that in mind before you average a column of counts.

A list in one column is another good use. The item count is the separator count plus one. That rule is wrong for an empty list, so the query guards it.

SELECT FeedbackID, Tags,
       CASE WHEN LEN(Tags) > 0 THEN REGEXP_COUNT(Tags, N';') + 1 ELSE 0 END AS tag_count
FROM dbo.Feedback;

A Data Quality Check

A valid email has exactly one @. Comments with a run of two or more spaces point to a messy import. One query can find both kinds of bad row.

SELECT FeedbackID, Email,
       REGEXP_COUNT(Email, N'@') AS at_signs,
       REGEXP_COUNT(Comment, N' {2,}') AS space_runs
FROM dbo.Feedback
WHERE ISNULL(REGEXP_COUNT(Email, N'@'), 0) <> 1
   OR REGEXP_COUNT(Comment, N' {2,}') > 0;

The ISNULL matters. A plain filter, REGEXP_COUNT(Email, N'@') <> 1, returned rows 2 and 4 only. The NULL email in row 5 compares as unknown, so the filter dropped it silently.

The Old LEN and REPLACE Trick

Before this function, you removed the target with REPLACE and compared the two lengths. It works for one character in clean data. The next query shows two places where it fails.

DECLARE @list nvarchar(40) = N'red green blue ';
DECLARE @pairs nvarchar(40) = N'a,,b,,c,,d';
SELECT LEN(@list) - LEN(REPLACE(@list, N' ', N'')) AS old_spaces,
       REGEXP_COUNT(@list, N' ') AS new_spaces,
       LEN(@pairs) - LEN(REPLACE(@pairs, N',,', N'')) AS old_no_divide,
       (LEN(@pairs) - LEN(REPLACE(@pairs, N',,', N''))) / LEN(N',,') AS old_divided,
       REGEXP_COUNT(@pairs, N',,') AS new_pairs;

LEN ignores trailing spaces, so the old trick missed the space at the end of the first string. A two-character target doubles the answer unless you divide by its length. The pattern version handled both without extra thought.

You could say the old trick is fine and a pattern is overkill. Fair point. For one visible character in clean data, REPLACE is short and runs on every version. REGEXP_COUNT earns its place when the target is a class like digits or a run like two spaces. It also wins for a word in any case or a match after a given position.

A Simple Rule

Reach for REGEXP_COUNT when the target is more than one plain character. Write the pattern for runs (\d+), not single characters, unless a single character is what you want. Guard NULL in every filter.

Remember that \w and [A-Za-z] cover English letters only. In my test, \w matched c, a and f in the word café and skipped the é. Add accented letters to the class when your data has them.

The database compatibility level didn’t gate the function in my test: it also ran at level 160 on this server. It does need SQL Server 2025 or later, and I didn’t test an older server.

When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE SqlRegexpCountDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SqlRegexpCountDemo;

REGEXP_COUNT is not a smarter way to count characters, it is a way to count patterns.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.