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:
123456
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 MatchValueFROM Documents tCROSS 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_1 | capture_group_2 |
|---|---|
| A | 12 |
| B | 34 |
| C | 56 |
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:
ABCDE
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:
ItemBatchRev
Enterprise Use Cases
1. Log Extraction
Extract codes from log messages:
SELECT LogId, m.valueFROM SystemLogCROSS APPLY REGEXP_MATCHES(LogText, 'ERR-[0-9]{4}') m;
2. Tokenizing User Input
Normalize free-text fields:
SELECT m.value AS TokenFROM UserInputCROSS APPLY REGEXP_SPLIT_TO_TABLE(InputValue, '\s+') m;
3. Parsing audit trails
SELECT m.valueFROM AuditTrailCROSS APPLY REGEXP_SPLIT_TO_TABLE(Details, ';') m;
4.Extracting structured identifiers
SELECT d.Id, m.value AS IdentifierFROM Documents dCROSS 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.valueFROM TextPatterns2025 tCROSS APPLY REGEXP_MATCHES(t.InputValue, '\d+') m;
Step 3. Extract all email addresses
SELECT t.Id, m.valueFROM TextPatterns2025 tCROSS 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.valueFROM TextPatterns2025 tCROSS APPLY REGEXP_SPLIT_TO_TABLE(t.InputValue, ',|;') s;
Step 5. Extract error codes
SELECT t.Id, m.valueFROM TextPatterns2025 tCROSS APPLY REGEXP_MATCHES(t.InputValue, 'ERR-[0-9]{4}') m;
Step 6. Parse version numbers
SELECT t.Id, m.valueFROM TextPatterns2025 tCROSS 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.


