Regular expressions (SQL Server 2025)

In one line: SQL Server 2025’s REGEXP_LIKE can express what LIKE can’t, but it’s case-sensitive whatever the collation, seeks only for some patterns, gets a fixed 9% row estimate, and cost 11 times the CPU of LIKE per row in the demo.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/regex-functions on GitHub.

What you’ll see

  • Rows missing after a LIKE became a REGEXP_LIKE. In a case-insensitive database, LIKE 'qz%' found 739 rows and REGEXP_LIKE(Code, '^qz') found 0.
  • A full index scan for a pattern that looks anchored. '^QZ' sought in 6 reads; '^QZ-\d' and '^QZ.*' read all 1,423 pages.
  • An estimate of 9% of the table on every REGEXP_LIKE: 45,000 rows estimated for 739.
  • Error 19300 for a pattern that works in .NET or Perl, and error 195 (“not a recognized built-in function name”) at compatibility level 160.

Why it happens

The functions use the RE2 engine, which is case-sensitive unless told otherwise and has no backreferences or lookarounds. Collation doesn’t apply to it.

The optimizer can derive an index range from some anchored literal prefixes, even from a variable, and then checks the pattern on the rows in the range. For other patterns it can’t, and it reads everything. It has no statistics to estimate a regular expression with, so it guesses a fixed 9% of the table.

Testing a regular expression costs more than LIKE‘s simple matching: 378 ms against 34 ms of CPU for the same substring over 500,000 rows.

What to do

  • Keep LIKE for what it can express: prefixes, substrings, simple character classes.
  • Use REGEXP_LIKE for formats LIKE can’t check, such as a whole email address. In the demo, one pattern found exactly the 5,000 malformed rows.
  • Add the 'i' flag when the old query relied on a case-insensitive collation.
  • Check the plan for each pattern on a big table, and narrow the rows with an ordinary predicate first where you can.
  • Run at compatibility level 170. REGEXP_LIKE and the table functions don’t exist below it; REGEXP_REPLACE does.

What doesn’t help

  • Assuming an anchored pattern seeks. Some do; the demo found no simple rule for which.
  • Copying patterns from application code without testing them. Lookarounds and backreferences fail with error 19300.

Check your server

-- SQL Server 2025+: modules that use REGEXP_ functions, and the
-- compatibility level of each database (REGEXP_LIKE needs 170).
SELECT OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
       OBJECT_NAME(m.object_id)        AS module_name
FROM sys.sql_modules AS m
WHERE m.definition LIKE '%REGEXP[_]%';

SELECT name, compatibility_level
FROM sys.databases
ORDER BY name;

See it happen

The regex-functions demo runs the same searches with LIKE and REGEXP_LIKE on 500,000 rows in a case-insensitive database. It measures case, index seeks, estimates and CPU, then tries the patterns RE2 rejects and the functions level 160 lacks. On 2022 and earlier it reports SKIPPED.

Related: non-SARGable predicates · implicit conversions · the json data type

Measured on SQL Server 2025 RTM-CU9-GDR (17.0.5015.4), Linux container. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights