SQL Server Regex Functions

October 29, 2026
| No Comments

Applies to SQL Server 2025 only. These functions don’t exist in SQL Server 2019 or 2022.

For years, if you wanted proper pattern matching in T-SQL you either bent LIKE and PATINDEX out of shape or reached for a CLR assembly. SQL Server 2025 finally ships native SQL Server regex functions, built on Google’s RE2 library. I’ve started using them for data cleansing jobs, and they replace a surprising amount of nested REPLACE and SUBSTRING code.

The SQL Server regex functions at a glance

There are seven of them. Five are scalar functions and two return tables:

  • REGEXP_LIKE – true or false: does the string match the pattern? Use it in a WHERE clause, a CASE expression or a CHECK constraint.
  • REGEXP_REPLACE – replaces matches with a new string. By default it replaces every occurrence.
  • REGEXP_SUBSTR – returns the matching text (the first match by default, or the Nth), or NULL if there’s no match.
  • REGEXP_INSTR – the start (or end) position of a match.
  • REGEXP_COUNT – how many times the pattern occurs.
  • REGEXP_MATCHES – a table of every match, with positions and captured groups.
  • REGEXP_SPLIT_TO_TABLE – splits a string on a pattern, one row per piece, with an ordinal.

Most of them take an optional flags argument: i for case-insensitive, c for case-sensitive, m for multi-line and s to let the dot match a newline.

A working example

Here’s the kind of thing I use them for: finding bad email addresses and tidying phone numbers in a contact table.

CREATE TABLE dbo.Contact
(
    ContactID int IDENTITY(1,1) PRIMARY KEY,
    Email     varchar(200) NOT NULL,
    Phone     varchar(30)  NULL
);

INSERT INTO dbo.Contact (Email, Phone)
VALUES ('[email protected]', '+44 (0)20 7946 0018'),
       ('[email protected]',          '0161-496-0000'),
       ('not an email',             '07700 900123'),
       ('missing@dot',              NULL);

-- Rows whose email doesn't look valid
SELECT ContactID, Email
FROM dbo.Contact
WHERE NOT REGEXP_LIKE(Email, '^[^@ ]+@[^@ ]+[.][a-z]{2,}$', 'i');

-- Strip everything except digits, and pull out the email domain
SELECT ContactID,
       REGEXP_REPLACE(Phone, '[^0-9]', '')      AS PhoneDigits,
       REGEXP_SUBSTR(Email, '@(.+)$', 1, 1, 'i', 1) AS EmailDomain,
       REGEXP_COUNT(Email, '[.]')               AS DotCount
FROM dbo.Contact;

The first query returns ‘not an email’ and ‘missing@dot’. In the second, REGEXP_REPLACE turns ‘+44 (0)20 7946 0018’ into ‘4402079460018’, and the last argument of REGEXP_SUBSTR (group 1) returns just the captured domain, not the @ sign as well.

The common mistake: treating REGEXP_LIKE like LIKE

The thing that catches people out is that LIKE matches the whole string, but a regular expression matches anywhere in the string unless you anchor it. So these two behave very differently:

-- LIKE: false, because the whole string must be three digits
SELECT CASE WHEN 'ABC123XYZ' LIKE '[0-9][0-9][0-9]' THEN 1 ELSE 0 END AS LikeResult;

-- REGEXP_LIKE: true, because three digits appear somewhere
SELECT CASE WHEN REGEXP_LIKE('ABC123XYZ', '[0-9]{3}') THEN 1 ELSE 0 END AS RegexResult;

-- Anchored: false, the same as LIKE
SELECT CASE WHEN REGEXP_LIKE('ABC123XYZ', '^[0-9]{3}$') THEN 1 ELSE 0 END AS AnchoredResult;

If you’re writing a validation rule, you almost always want ^ at the start and $ at the end. Without them, a “postcode check” will happily accept a paragraph of text that has a postcode buried in it.

The second mistake is assuming the column collation decides case sensitivity. It doesn’t. The regex functions are case-sensitive by default even on a case-insensitive database, so ‘[email protected]’ would fail my email check without the i flag.

Watch out for

  • Compatibility level. REGEXP_LIKE, REGEXP_MATCHES and REGEXP_SPLIT_TO_TABLE need database compatibility level 170. The other scalar functions work at any level on a 2025 instance. If you’ve upgraded but left the database at 150 or 160, REGEXP_LIKE won’t be recognised.
  • No boolean column. REGEXP_LIKE is a predicate, so you can’t just put it in a SELECT list. Wrap it in a CASE expression as above.
  • Performance. A REGEXP_LIKE in the WHERE clause can’t seek on an index, so expect a scan. Filter on something sargable first where you can, and test on real data volumes.
  • Size limits. varchar(max) and nvarchar(max) inputs are supported but limited to 2 MB, and the pattern itself is limited to 8,000 bytes.
  • RE2 isn’t PCRE. There are no lookaheads, lookbehinds or backreferences, so a pattern copied from a .NET or Perl example may not work.
  • Natively compiled modules. The functions aren’t supported in natively compiled stored procedures.

Further reading

Microsoft’s overview, with the full RE2 syntax, is at Regular expressions functions (Transact-SQL). If you’re looking at other new 2025 features, my post on the SQL Server vector data type covers another one, and there’s more in the SQL Server DBA Handbook.

This topic came up in the SQLServerCentral Question of the Day: RegEx Functions IV.

Clive Roberts DBA

Question of the Day, SQL Server, T-SQL