RegEx Comes to T-SQL: The New REGEXP Functions in SQL Server 2025

If you run a scraper that collects competitor prices, your table probably has rows like these: “Sunflower Oil 5 LT”, “SUNFLOWER OIL 5L Pet”, “Sunflower oil 5 lt.”. Same product, three spellings. For years, cleaning that up in T-SQL meant LIKE, PATINDEX, nested REPLACE chains or CLR functions.

SQL Server 2025 changes that: T-SQL now has built-in regular expression (RegEx) functions. In this post I walk through all seven, with retail examples and the traps worth knowing before you put them into production.

Why it matters

LIKE only understands %, _ and [ ]. You can’t express a rule like “a number, maybe a space, then kg/g/l” with LIKE. Until now the options were:

• Hard-to-read PATINDEX + SUBSTRING combinations
• Pulling the data into Python or SSIS and cleaning it there
• Deploying a CLR assembly (and convincing the security team)

Now that logic fits in one line, inside the query.

Seven new functions

Function	What it does
REGEXP_LIKE	Does the text match the pattern? (true/false)
REGEXP_COUNT	How many times does the pattern occur?
REGEXP_INSTR	Position where a match starts (or ends)
REGEXP_SUBSTR	Returns the matching part
REGEXP_REPLACE	Replaces matches with other text
REGEXP_MATCHES	Table-valued: one row per match
REGEXP_SPLIT_TO_TABLE	Splits text on a pattern into rows

The last two are table-valued functions, used with CROSS APPLY.

Prerequisites

• Version: SQL Server 2025 and later. Also in Azure SQL Database and SQL database in Fabric. Azure SQL Managed Instance needs the “SQL Server 2025” or “Always-up-to-date” update policy. Not available in SQL Server 2022 or earlier.
• Compatibility level: REGEXP_LIKE needs database compatibility level 170. The other RegEx functions work at every level.

ALTER DATABASE [Retail] SET COMPATIBILITY_LEVEL = 170;

Changing the compatibility level can affect query plans too, so try it in a test environment first.

Flags: i case-insensitive, c case-sensitive (the default), m multi-line (^ and $ also match at line breaks), s lets . match newlines. If flags conflict, the last one wins: 'ic' is case-sensitive.

Four real retail examples

1. Pull pack size out of the product name

SELECT product_name,
       REGEXP_SUBSTR(product_name, '[0-9]+([.,][0-9]+)? ?(kg|gr|g|lt|l|ml)\b', 1, 1, 'i') AS pack_size
FROM dbo.CompetitorProduct;
-- 'Sunflower Oil  5 LT'     -> '5 LT'
-- 'Granulated Sugar 1.5 kg' -> '1.5 kg'

That one column is the first step towards comparing competitor prices like for like.

2. Validate barcodes at the table level

ALTER TABLE dbo.Product
ADD CONSTRAINT CK_Product_Barcode
CHECK (REGEXP_LIKE(barcode, '^([0-9]{8}|[0-9]{13})$'));

Barcodes that aren’t 8 or 13 digits, or that contain letters or spaces, can no longer get into the table. (This needs compatibility level 170.)

3. Clean up messy text

SELECT
  REGEXP_REPLACE(product_name, '\s{2,}', ' ')   AS single_spaced,
  REGEXP_REPLACE(price_text, '[^0-9,]', '')      AS digits_only
FROM dbo.CompetitorProduct;
-- '₺ 1.249,90 TL'  -> '1249,90'

After the second expression, swap the comma for a dot and use TRY_CAST(... AS decimal(10,2)) to get a number.

4. Split a tag list into rows

SELECT p.product_id, s.value AS tag
FROM dbo.Product AS p
CROSS APPLY REGEXP_SPLIT_TO_TABLE(p.tags, '\s*[,;|]\s*') AS s;

STRING_SPLIT takes a single separator; here commas, semicolons and pipes all work at once.

Before you ship: the traps

Performance and index use. REGEXP_LIKE is SARGable only when the pattern starts with ^, which lets it use an index seek. Unanchored patterns scan the whole table. On large tables, run RegEx once during ETL and store the result in its own column instead of running it in every report query.

Turkish characters (and other languages). RegEx functions don’t follow collation rules the way LIKE does. The i flag doesn’t apply language-specific case rules, so with Turkish pairs like İ/i and I/ı you can get different results from LIKE. Test patterns against real data, and spell letters out where it matters (for example [İi], [Iı]).

Size limits. varchar(max)/nvarchar(max) inputs are supported up to 2 MB; the pattern can be up to 8,000 bytes.

Cardinality estimates. The optimizer can struggle to estimate how selective a RegEx predicate is. If plans look off, look at the ASSUME_FIXED_MIN_SELECTIVITY_FOR_REGEXP and ASSUME_FIXED_MAX_SELECTIVITY_FOR_REGEXP query hints.

Bonus: fuzzy matching (preview)

SQL Server 2025 also adds EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, JARO_WINKLER_DISTANCE and JARO_WINKLER_SIMILARITY. They score how close “Sunflower Oil 5L” is to “Sunflwr Oil 5 L” on a 0-100 scale, which is very handy for matching competitor products to your own product master.

These are still in preview, though: you turn them on with ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;, and they only work with Windows and binary collations (SQL_* collations aren’t supported). Try them in test, not in production.

Wrapping up

RegEx was a long-standing “nice to have” for T-SQL; with SQL Server 2025 it’s here. A good share of cleaning, validation and text extraction work can now happen in the database without a trip through an ETL tool.

My advice: start with a validation CHECK constraint. It’s low risk and it stops dirty data getting in straight away. Which REPLACE chain would you rewrite with RegEx first?

Sources

• REGEXP_LIKE (Transact-SQL) – Microsoft Learn
• Fuzzy string matching overview – Microsoft Learn
• Using RegEx in SQL Server 2025: Complete Guide – Redgate Simple Talk
• T-SQL in SQL Server 2025: REGEXP_LIKE – SQLServerCentral

STAY IN THE LOOP

New guides, sorted by topic in a single email.

Every Monday, the week's new posts arrive grouped by topic. No new posts, no email.

WEEKLY NEWSLETTER

Microsoft FabricPower BIDAXSQL Server & T-SQLPython

E-posta Subscribe

I have read the privacy notice and agree to my email address being processed for the weekly newsletter.

Nothing is sent until you click the link in the confirmation email. Unsubscribe anytime with one click.