Regex finally lands in T-SQL

For about twenty years the answer to “can SQL Server do regular expressions?” was “sort of”. You could write a CLR assembly, push the work to the application, or build a chain of nested PATINDEX, SUBSTRING and REPLACE calls that nobody could maintain six months later. SQL Server 2025 finally gives us native regular expression functions in T-SQL.

In my last post, Your users don’t search the way your LIKE clause does, I compared keyword, full-text and vector search. Regex was the obvious gap in that post. It isn’t a search engine, but it is the right tool for a lot of the pattern problems that we’ve been forcing LIKE to solve.

What you get

SQL Server 2025 ships seven regex functions:

 

Function	Type	What it does
REGEXP_LIKE	Scalar	Returns true if the string matches the pattern
REGEXP_COUNT	Scalar	Counts how many times the pattern matches
REGEXP_INSTR	Scalar	Returns the start or end position of a match
REGEXP_REPLACE	Scalar	Replaces matches with a replacement string
REGEXP_SUBSTR	Scalar	Returns the text of a match
REGEXP_MATCHES	Table-valued	Returns every match as rows
REGEXP_SPLIT_TO_TABLE	Table-valued	Splits a string on a pattern and returns the pieces as rows

The compatibility level catch

This is the first thing to check. While Microsoft introduced these functions with the release of SQL Server 2025, some have been back ported and work when set at a compatibility level of 160:

• REGEXP_COUNT
• REGEXP_REPLACE
• REGEXP_SUBSTR
• REGEXP_INSTR

The remaining 3 require a compatibility level of 170:

• REGEXP_LIKE
• REGEXP_MATCHES
• REGEXP_SPLIT_TO_TABLE

Upgrading the instance doesn’t change your database compatibility level. A restored or upgraded database stays where it was, and most of the databases I see on client systems are still at 150 or 160.

 

— Check where you are
SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

— Only after you’ve tested the workload at 170
ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 170;

Don’t change the compatibility level just to use one function. Level 170 also changes optimiser behaviour, so treat it as a proper upgrade step: capture a Query Store baseline first, then compare.

Validation: the LIKE pattern you no longer need

Here’s a typical check for Australian mobile numbers that are stored in a mix of formats:

SELECT CustomerID, Mobile
FROM dbo.Customer
WHERE NOT REGEXP_LIKE(Mobile, ‘^(\+?61|0)4\d{2}[ ]?\d{3}[ ]?\d{3}$’);

This finds every mobile number that doesn’t match a valid format. Doing the same with LIKE takes several OR conditions and still misses edge cases.

It also works as a CHECK constraint, so bad data is stopped at the door:

ALTER TABLE dbo.Customer
ADD CONSTRAINT CK_Customer_Email
CHECK (REGEXP_LIKE(Email, ‘^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$’));

Cleansing: REGEXP_REPLACE

Strip everything except digits from a phone number before you compare it:

SELECT Mobile,
REGEXP_REPLACE(Mobile, ‘[^0-9]’, ”) AS MobileDigitsOnly
FROM dbo.Customer;

Collapse repeated spaces in a free-text column:

UPDATE dbo.Product
SET Description = REGEXP_REPLACE(Description, ‘\s{2,}’, ‘ ‘)
WHERE REGEXP_COUNT(Description, ‘\s{2,}’) > 0;

Extraction: REGEXP_SUBSTR and REGEXP_MATCHES

Pull an order reference out of an email subject line:

SELECT Subject,
REGEXP_SUBSTR(Subject, ‘ORD-[0-9]{6}’) AS OrderRef
FROM dbo.InboundEmail;

If a subject line can hold several references, REGEXP_MATCHES returns one row per match, which you can use with CROSS APPLY:

SELECT e.EmailID, m.match_value
FROM dbo.InboundEmail AS e
CROSS APPLY REGEXP_MATCHES(e.Subject, ‘ORD-[0-9]{6}’) AS m;

Case sensitivity is not what you expect

Regex in SQL Server is case-sensitive by default, whatever the collation says. That catches people out because LIKE follows the collation. Pass the 'i' flag when you want a case-insensitive match:

SELECT * FROM dbo.Product
WHERE REGEXP_LIKE(ProductName, ‘^widget’, ‘i’);

The DBA bit: performance

Regex is not a free lunch, so keep these points in mind:

• Whether it can seek depends on the pattern. The first SQL Server 2025 builds scanned for every regex predicate. On current builds (tested on SQL Server 2025 CU10), a pattern anchored with ^ and a literal prefix, such as ‘^user99’, can use an index seek just like LIKE ‘user99%’. Unanchored patterns, or patterns that start with a character class, still read and evaluate every row. Watch the row estimates too: in my test the regex seek estimated 9,000 rows for 1,111 actual, while the equivalent LIKE estimated 1,135. In a bigger query, a guess that far out can lead to a poor join choice or memory grant. On a large table, filter with something indexable first and apply the regex to the smaller set that’s left.
• It costs CPU. Complex patterns with heavy backtracking burn CPU on every row. Test with production-sized data, not a 1,000-row dev table.
• LOB support came later. Support for varchar(max) and nvarchar(max) inputs (up to 2 MB) arrived across all seven functions in SQL Server 2025 CU5. Check your build before you point regex at large text columns.
• It doesn’t replace full-text search. For “find documents that mention this word” on large tables, full-text indexes are still the right tool.

Where I’d use it first

The quickest wins are data quality checks: finding bad emails, phone numbers, postcodes and ABNs before they cause problems downstream. Next is retiring old CLR regex assemblies, which removes a deployment and security headache on every server they’re installed on.

If you’d like some help improving the performance of your SQL Server environment, get in touch with us at SQL Masters Consulting.