SQL Server 2025: REGEXP_MATCHES() and REGEXP_SPLIT_TO_TABLE() (Part 9)

Introduction

SQL Server 2025 continues expanding its native Regular Expression feature set with two of the most powerful text-processing functions in the release:

• REGEXP_MATCHES() – returns a table containing all substrings that match a pattern
• REGEXP_SPLIT_TO_TABLE() – splits a string into rows using a RegEx delimiter

These functions significantly improve how SQL Server handles parsing, extraction, tokenization, and advanced text analysis. Before SQL Server 2025, these tasks required CLR functions, XML workarounds, STRING_SPLIT hacks, or external ETL. Now they are built directly into the engine.

This article provides a full deep dive into both functions with enterprise-grade examples and use cases.


REGEXP_MATCHES(): Extract All Matches

Overview

REGEXP_MATCHES returns a table rather than a single scalar value.
Each row is one captured match.

Syntax

REGEXP_MATCHES(expression, pattern [, flags])

Basic Example

Extract all numbers:

SELECT *
FROM REGEXP_MATCHES('Item12Batch34Rev56', '\d+');

Output:

12
34
56

Extract All Email Addresses From Text

SELECT *
FROM REGEXP_MATCHES(
'Contact hr@company.com or support@company.com for assistance.',
'[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}'
);

Extract All Dates

SELECT *
FROM REGEXP_MATCHES(
'System updated on 2025-01-10, next update 2025-02-14.',
'\d{4}-\d{2}-\d{2}'
);

Use With CROSS APPLY

Return matched values alongside the source row:

SELECT t.Id, m.value AS MatchValue
FROM Documents t
CROSS APPLY REGEXP_MATCHES(t.Content, '[A-Z]{3}-[0-9]{5}') m;

This is extremely useful for auditing reference IDs inside text fields.


Extract Multiple Captured Groups

Use parenthesized capture groups:

SELECT *
FROM REGEXP_MATCHES('A12 B34 C56', '([A-Z])(\d+)');

Returns rows like:

capture_group_1capture_group_2
A12
B34
C56

REGEXP_SPLIT_TO_TABLE(): Splitting Text into Rows

Overview

REGEXP_SPLIT_TO_TABLE splits a string based on a regular expression delimiter and returns each segment as a row.

Syntax

REGEXP_SPLIT_TO_TABLE(expression, pattern [, flags])

Basic Example

Split on commas or semicolons:

SELECT *
FROM REGEXP_SPLIT_TO_TABLE('A,B;C;D,E', ',|;');

Returns:

A
B
C
D
E

Split Multi-Delimiter Address Fields

SELECT *
FROM REGEXP_SPLIT_TO_TABLE('Alpha Beta Gamma', '\s+');

Split Log Line Into Key-Value Pairs

SELECT *
FROM REGEXP_SPLIT_TO_TABLE(
'key1=value1;key2=value2;key3=value3',
';'
);

Split CSV Values With Optional Whitespace

SELECT *
FROM REGEXP_SPLIT_TO_TABLE('A, B, C, D', '\s*,\s*');

Split Only When a Pattern Is Matched

Split on a digit sequence:

SELECT *
FROM REGEXP_SPLIT_TO_TABLE('Item12Batch34Rev56', '\d+');

Returns:

Item
Batch
Rev

Enterprise Use Cases

1. Log Extraction

Extract codes from log messages:

SELECT LogId, m.value
FROM SystemLog
CROSS APPLY REGEXP_MATCHES(LogText, 'ERR-[0-9]{4}') m;

2. Tokenizing User Input

Normalize free-text fields:

SELECT m.value AS Token
FROM UserInput
CROSS APPLY REGEXP_SPLIT_TO_TABLE(InputValue, '\s+') m;

3. Parsing audit trails

SELECT m.value
FROM AuditTrail
CROSS APPLY REGEXP_SPLIT_TO_TABLE(Details, ';') m;

4.Extracting structured identifiers

SELECT d.Id, m.value AS Identifier
FROM Documents d
CROSS APPLY REGEXP_MATCHES(d.DocumentBody, '[A-Z]{3}-[0-9]{5}') m;

5. Extracting IPv4 addresses

SELECT *
FROM REGEXP_MATCHES(
'Ping logs: 192.168.1.10 OK; 10.0.0.5 OK;',
'\b(?:\d{1,3}\.){3}\d{1,3}\b'
);

Performance Considerations

Both REGEXP_MATCHES and REGEXP_SPLIT_TO_TABLE are:

• Table-valued functions
• Evaluated row-by-row
• Non-SARGable
• CPU-intensive on very large inputs

Best practices:

• Use VARCHAR(n) when possible
• Pre-filter with LIKE
• Apply to staging tables for ETL
• Avoid evaluating very large NVARCHAR(MAX) fields frequently
• Use CROSS APPLY for targeted pattern extraction

These functions shine in data cleansing, analytics, auditing, monitoring systems, and one-time transformations.


Workshop: Hands-On With REGEXP_MATCHES and SPLIT_TO_TABLE

Step 1. Create sample table

CREATE TABLE TextPatterns2025 (
Id INT IDENTITY,
InputValue VARCHAR(300)
);
INSERT INTO TextPatterns2025 (InputValue)
VALUES
('Item12Batch34Rev56'),
('Contact hr@company.com and admin@company.com'),
('A,B;C;D,E'),
('Error codes: ERR-1001; ERR-2002; OK'),
('Version15.3.9 release notes');

Step 2. Extract all numeric sequences

SELECT t.Id, m.value
FROM TextPatterns2025 t
CROSS APPLY REGEXP_MATCHES(t.InputValue, '\d+') m;

Step 3. Extract all email addresses

SELECT t.Id, m.value
FROM TextPatterns2025 t
CROSS APPLY REGEXP_MATCHES(
t.InputValue,
'[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}'
) m;

Step 4. Split by commas or semicolons

SELECT t.Id, s.value
FROM TextPatterns2025 t
CROSS APPLY REGEXP_SPLIT_TO_TABLE(t.InputValue, ',|;') s;

Step 5. Extract error codes

SELECT t.Id, m.value
FROM TextPatterns2025 t
CROSS APPLY REGEXP_MATCHES(t.InputValue, 'ERR-[0-9]{4}') m;

Step 6. Parse version numbers

SELECT t.Id, m.value
FROM TextPatterns2025 t
CROSS APPLY REGEXP_MATCHES(t.InputValue, '\d+(\.\d+)+') m;

Final Thoughts

REGEXP_MATCHES and REGEXP_SPLIT_TO_TABLE dramatically expand SQL Server’s native text-processing power. They allow table-valued extraction, tokenization, and document parsing directly inside the engine. When used correctly, these tools streamline ETL, validation, auditing, and log processing tasks that previously required external scripting languages or custom CLR solutions.

The final RegEx article comes tomorrow with Day 10, which covers external REST endpoint invocation, one of SQL Server 2025’s most modern and cloud-forward features.


References

• REGEXP_MATCHES Documentation
https://learn.microsoft.com/sql/t-sql/functions/regexp-matches-transact-sql

• REGEXP_SPLIT_TO_TABLE Documentation
https://learn.microsoft.com/sql/t-sql/functions/regexp-split-to-table-transact-sql

• Regular Expression Syntax Reference
https://learn.microsoft.com/sql/t-sql/functions/regular-expression-reference

• SQL Server 2025 New Features
https://learn.microsoft.com/sql/sql-server/what-s-new-in-sql-server-2025


Discover more from SQLYARD

Subscribe to get the latest posts sent to your email.

Leave a Comment