What is Regex?

The other day, while building LEGO with my 3-year-old — an activity that’s equal parts joy and chaos — I spent minutes digging for one tiny piece and thought, “If only Regex worked on LEGO.”

That moment of playful frustration turned into a perfect metaphor.

Think of your LEGO box as a pile of data — a colorful jumble of tiny pieces. Now imagine trying to find every little brick from a specific LEGO set your kid mixed into the pile. That’s tricky — you’d have to sift through each piece one by one.

But what if you had a smart filter that instantly found exactly those pieces?

That’s what Regex (short for Regular Expressions) does for your data. It’s a powerful pattern-matching tool that helps you search, extract, and transform text with precision.

With Regex now natively supported in SQL Server 2025 and Azure SQL, this capability is built directly into T-SQL — no external languages or workarounds required.

What can Regex help you do?

• Enhancing data quality and accuracy by validating and correcting formats like phone numbers, email addresses, zip codes, and more.
• Extracting valuable insights by identifying and grouping specific text patterns such as keywords, hashtags, or mentions.
• Transforming and standardizing data by replacing, splitting, or joining text patterns — useful for handling abbreviations, acronyms, or synonyms.
• Cleaning and optimizing data by removing unwanted patterns like extra whitespace, punctuation, or duplicates.

Meet the new Regex functions in T-SQL

SQL Server 2025 introduces seven new T-SQL Regex functions, grouped into two categories: scalar functions (return a value per row) and table-valued functions (TVFs) (return a set of rows). Here’s a quick overview:

These functions follow the POSIX standard and support most of the PCRE/PCRE2 flavor of regular expression syntax, making them compatible with most modern Regex engines and tools.

• Character classes (\d, \w, etc.)
• Quantifiers (+, *, {n})
• Alternation (|)
• Capture groups ((...))

• 'i' – Case-insensitive matching
• 'm' – Multi-line mode (^ and $ match line boundaries)
• 's' – Dot matches newline
• 'c' – Case-sensitive matching (default)

Examples: Regex in Action

Let’s explore how these functions solve tricky real-world data tasks that were hard to do in earlier SQL versions.

REGEXP_LIKE: Data Validation — Keeping data in shape

SELECT [Name],
       Email,
       CASE WHEN REGEXP_LIKE (Email, '^[A-Za-z0-9._+]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') THEN 'Valid Email' ELSE 'Invalid Email' END AS IsValidEmail
FROM   (VALUES ('John Doe', 'john@contoso.com'), ('Alice Smith', 'alice@fabrikam.com'), ('Bob Johnson', 'bob@fabrikam.net'), ('Charlie Brown', 'charlie@contoso.co.in'), ('Eve Jones', 'eve@@contoso.com')) AS e(Name, Email);

We can further use REGEXP_LIKE in CHECK constraints to enforce these rules at the column level (so no invalid format ever gets into the table). For instance:

CREATE TABLE Employees (
    ...,
    Email VARCHAR (320) CHECK (REGEXP_LIKE (Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')),
    Phone VARCHAR (20)  CHECK (REGEXP_LIKE (Phone, '^(\d{3})-(\d{3})-(\d{4})$'))
);

This level of enforcement significantly enhances data integrity by ensuring that only correctly formatted values are accepted into the database.

REGEXP_COUNT: Count JSON object keys

SELECT JsonData,
       REGEXP_COUNT(JsonData, '"[^"]+"\s*:', 1, 'i') AS NumKeys
FROM   (VALUES ('{"name":"Abhiman","role":"PM","location":"Bengaluru"}'), ('{"skills":["SQL","T-SQL","Regex"],"level":"Advanced"}'), ('{"project":{"name":"Regex GA","status":"Live"},"team":["Tejas","UC"]}'), ('{"empty":{}}'), ('{}')) AS t(JsonData);

REGEXP_INSTR: Locate patterns in logs

Find the position of the first error code (ERR-XXXX) in log messages — even when the pattern appears multiple times or in varying locations.

SELECT LogMessage,
       REGEXP_INSTR(LogMessage, 'ERR-\d{4}', 1, 1, 0, 'i') AS ErrorCodePosition
FROM   (VALUES ('System initialized. ERR-1001 occurred during startup.'), ('Warning: Disk space low. ERR-2048. Retry failed. ERR-2049.'), ('No errors found.'), ('ERR-0001: Critical failure. ERR-0002: Recovery started.'), ('Startup complete. Monitoring active.')) AS t(LogMessage);

REGEXP_REPLACE: Redact sensitive data

SELECT sensitive_info,
       REGEXP_REPLACE(sensitive_info, '(\d{3}-\d{2}-\d{4}|\d{4}-\d{4}-\d{4}-\d{4})', '***-**-****') AS redacted_info
FROM   (VALUES ('John Doe SSN: 123-45-6789'), ('Credit Card: 9876-5432-1098-7654'), ('SSN: 000-00-0000 and Card: 1111-2222-3333-4444'), ('No sensitive info here'), ('Multiple SSNs: 111-22-3333, 222-33-4444'), ('Card: 1234-5678-9012-3456, SSN: 999-88-7777')) AS t(sensitive_info);

SELECT   REGEXP_SUBSTR(Email, '@(.+)$', 1, 1, 'i', 1) AS Domain,
         COUNT(*) AS NumUsers
FROM     (VALUES ('Alice', 'alice@contoso.com'), ('Bob', 'bob@fabrikam.co.in'), ('Charlie', 'charlie@example.com'), ('Diana', 'diana@college.edu'), ('Eve', 'eve@contoso.com'), ('Frank', 'frank@fabrikam.co.in'), ('Grace', 'grace@example.net')) AS e(Name, Email)
WHERE    REGEXP_LIKE (Email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$')
GROUP BY REGEXP_SUBSTR(Email, '@(.+)$', 1, 1, 'i', 1);

SELECT *
FROM   REGEXP_MATCHES ('Contact us at support@example.com or sales@example.com', '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}');

SELECT *
FROM   REGEXP_SPLIT_TO_TABLE ('Name: John Doe; Email: john.doe@example.com; Phone: 123-456-7890', '; ');

Your feedback and partnership continue to drive innovation in Azure SQL and SQL Server — thank you for being part of it.