SQL Server 2025 Regex Support: Native T-SQL Expressions
SQL Server 2025 && Regex = Less Code, Less Pain in your queries
Hi Guys,
Here’s some good news: after years of workarounds, hacks, and tricks, SQL Server 2025 finally ships with native regex support in T-SQL. And no, this isn’t just a nice-to-have. It’s a game changer for anyone who deals with messy data, phone numbers, emails, codes… you name it.
For years we’ve had to rely on ugly patterns like LIKE '%...%'
, CHARINDEX
, SPLIT
, or even external CLR functions.
They worked, but the cost in complexity, performance, and bugs was too high.
Finally: native regex in SQL Server 2025
That’s right. Now you get built-in functions such as:
REGEXP_LIKE(...)
REGEXP_REPLACE(...)
REGEXP_SUBSTR(...)
REGEXP_COUNT
,REGEXP_SPLIT_TO_TABLE
,REGEXP_INSTR
,REGEXP_MATCHES
(table-valued functions are rolling out too)
All powered by the RE2 engine: fast, familiar, and compatible enough with PCRE and .NET regex syntax.
What changes?
- Parsing complex strings in plain T-SQL — finally readable.
- Less code, fewer bugs.
- Good performance, but keep in mind: for simple patterns like
LIKE 'abc%'
, the old operators are still faster.
A practical example: validating phone numbers
Let’s say we’ve got a PhoneNumber
column, and we want to select only the valid ones:
- Italian numbers (
+39 345 1234567
) - National with or without separators
- Filter out the garbage
With native regex:
-- filter only valid numbers
SELECT *
FROM Customers
WHERE REGEXP_LIKE(
PhoneNumber,
'^(\+39\s?)?3\d{2}[\s\/]?\d{6,7}$',
'i'
);
^(\+39\s?)?
→ optional country code with spaces3\d{2}
→ Italian mobile prefix[\s\/]?
→ optional space or slash\d{6,7}
→ 6–7 digit number'i'
→ case-insensitive flag
Clean the number (digits only)
SELECT
REGEXP_REPLACE(
PhoneNumber,
'[^\d+]',
'',
1,
0,
'g'
) AS PurePhone
FROM Customers;
Here g
(global replace) removes everything except digits and +
.
Extract the number directly
SELECT
REGEXP_SUBSTR(
PhoneNumber,
'(\+?39\s?)?3\d{2}\d{6,7}',
1,
1,
'',
0
) AS MatchedNumber
FROM Customers;
Why is this a real breakthrough?
- No more ugly nested functions.
- Regex patterns are readable and maintainable.
- Validation happens in-database, avoiding round-trips to the app.
Quick wrap-up
SQL Server 2025 finally gives us native regex. It feels like a modern feature, but now it’s core SQL Server.
Use it wisely (regex can be expensive), but whenever you need advanced filtering or validation — it’s the right tool.
If you’re interested, I can prepare a follow-up post on regex & index tuning, showing how to normalize strings first so your queries still benefit from indexes.
Stay tuned and keep performance tight!
Luca
Comments
Post a Comment