Now You Have Two Problems

An Introduction to Regular Expressions in SQL Server 2025

Kevin Feasel (@feaselkl)
https://csmore.info/on/regex

Who Am I? What Am I Doing Here?

Catallaxy Services
Curated SQL
Finding Ghosts in Your Data

@feaselkl

Motivation

SQL Server has historically relied on LIKE and PATINDEX() for pattern matching. That's two wildcards (%, _) and not much else.

SQL Server 2025 introduces native regular expression support.

Regex adds capabilities to unlock text processing that was previously difficult or impossible in T-SQL alone.

LIKE still wins on simple prefix/suffix matching, so reach for regex when the pattern genuinely needs it.

Agenda

  1. What Are Regular Expressions?
  2. Regex Fundamentals
  3. REGEXP_LIKE
  4. REGEXP_COUNT
  5. REGEXP_REPLACE
  6. REGEXP_SUBSTR
  7. REGEXP_INSTR
  8. Practical Examples

What Are Regular Expressions?

A regular expression (regex) is a sequence of characters that defines a search pattern.

Think of them as a super-powered version of the LIKE operator.

The Famous Joke

"Some people, when confronted with a problem, think 'I know, I'll use regular expressions.' Now they have two problems."

-- Jamie Zawinski

Let's see if that's still true.

Agenda

  1. What Are Regular Expressions?
  2. Regex Fundamentals
  3. REGEXP_LIKE
  4. REGEXP_COUNT
  5. REGEXP_REPLACE
  6. REGEXP_SUBSTR
  7. REGEXP_INSTR
  8. Practical Examples

Regex Cheat Sheet

Regex cheat sheet

Greedy vs. Lazy Quantifiers

By default, quantifiers are greedy: they match as much as possible.

Adding ? after a quantifier makes it lazy: it matches as little as possible.

  • <.+> on <b>bold</b> matches <b>bold</b> (greedy: everything between the first < and the last >)
  • <.+?> on the same string matches <b> (lazy: stops at the first >)

This is the #1 source of "my regex matches too much!" bugs.

Demo Time

Agenda

  1. What Are Regular Expressions?
  2. Regex Fundamentals
  3. REGEXP_LIKE
  4. REGEXP_COUNT
  5. REGEXP_REPLACE
  6. REGEXP_SUBSTR
  7. REGEXP_INSTR
  8. Practical Examples

REGEXP_LIKE

REGEXP_LIKE tests whether a string matches a regular expression pattern. It returns 1 (match) or 0 (no match).


						REGEXP_LIKE(
							source_string,
							pattern
							[, flags]
						)

Think of this as a more powerful replacement for LIKE or PATINDEX() > 0.

REGEXP_LIKE Flags

The optional flags parameter modifies matching behavior:

  • 'i' -- Case-insensitive matching
  • 'm' -- Multi-line mode (^ and $ match line boundaries)

These flags are common to all SQL Server 2025 regex functions.

Demo Time

Agenda

  1. What Are Regular Expressions?
  2. Regex Fundamentals
  3. REGEXP_LIKE
  4. REGEXP_COUNT
  5. REGEXP_REPLACE
  6. REGEXP_SUBSTR
  7. REGEXP_INSTR
  8. Practical Examples

REGEXP_COUNT

REGEXP_COUNT returns the number of times a pattern matches within a string.


						REGEXP_COUNT(
							source_string,
							pattern
							[, start_position
							[, flags]]
						)

This is useful for counting occurrences of a pattern, such as the number of words or digits in a string.

Demo Time

Agenda

  1. What Are Regular Expressions?
  2. Regex Fundamentals
  3. REGEXP_LIKE
  4. REGEXP_COUNT
  5. REGEXP_REPLACE
  6. REGEXP_SUBSTR
  7. REGEXP_INSTR
  8. Practical Examples

REGEXP_REPLACE

REGEXP_REPLACE replaces occurrences of a pattern within a string.


						REGEXP_REPLACE(
							source_string,
							pattern,
							replacement
							[, start_position
							[, occurrence
							[, flags]]]
						)

This is a major upgrade from REPLACE(), which only handles literal string replacement.

Backreferences in Replacement

Use capture groups in your replacement string:

  • \1, \2, etc. refer to capture groups
  • Allows you to rearrange, reformat, or selectively keep parts of the matched text

For example, (\d{3})-(\d{4}) with replacement \2-\1 swaps the two groups.

Demo Time

Agenda

  1. What Are Regular Expressions?
  2. Regex Fundamentals
  3. REGEXP_LIKE
  4. REGEXP_COUNT
  5. REGEXP_REPLACE
  6. REGEXP_SUBSTR
  7. REGEXP_INSTR
  8. Practical Examples

REGEXP_SUBSTR

REGEXP_SUBSTR extracts a substring that matches a regular expression pattern.


						REGEXP_SUBSTR(
							source_string,
							pattern
							[, start_position
							[, occurrence
							[, flags
							[, group_number]]]]
						)

This is especially useful for extracting structured data from unstructured text.

Demo Time

Agenda

  1. What Are Regular Expressions?
  2. Regex Fundamentals
  3. REGEXP_LIKE
  4. REGEXP_COUNT
  5. REGEXP_REPLACE
  6. REGEXP_SUBSTR
  7. REGEXP_INSTR
  8. Practical Examples

REGEXP_INSTR

REGEXP_INSTR returns the starting position of a pattern match within a string. Think of it as a regex-powered PATINDEX().


						REGEXP_INSTR(
							source_string,
							pattern
							[, start_position
							[, occurrence
							[, return_option
							[, flags
							[, group_number]]]]]
						)

The return_option parameter controls whether to return the start (0) or end (1) of the match.

Demo Time

Agenda

  1. What Are Regular Expressions?
  2. Regex Fundamentals
  3. REGEXP_LIKE
  4. REGEXP_COUNT
  5. REGEXP_REPLACE
  6. REGEXP_SUBSTR
  7. REGEXP_INSTR
  8. Practical Examples

Unicode Property Escapes

SQL Server 2025 uses the ICU regex engine, which supports Unicode property escapes. This is something LIKE and PATINDEX cannot do.

  • \p{L} -- Any Unicode letter (Latin, Cyrillic, Chinese, Arabic, etc.)
  • \p{N} -- Any Unicode number
  • \p{Sc} -- Any currency symbol ($, €, £, ¥)
  • \p{P} -- Any punctuation character

Essential for working with multilingual or international data.

Practical Examples

Now let's put it all together with some real-world scenarios:

  • Data validation (e-mail addresses, phone numbers)
  • Parsing semi-structured log data
  • Data cleansing and standardization pipelines
  • Unicode-aware text processing
  • Extracting structured data from free text

Demo Time

Performance Considerations

Regular expressions are powerful but come with caveats:

  • Regex functions in a WHERE clause might not use indexes. Some require a full scan, just like LIKE '%pattern%'
  • For simple prefix matching, LIKE 'prefix%' is still faster and can use indexes
  • Filter with indexed columns first, then apply regex for validation or extraction

Regex Prior to SQL Server 2025

If you want to use regular expressions in SQL Server before 2025, you would typically rely on CLR functions or third-party libraries.

One outstanding CLR-based library is SQL#, which provides a comprehensive set of regex functions for SQL Server.

The free version includes most Regex functionality and the paid edition adds capture groups.

Wrapping Up

SQL Server 2025 brings long-awaited regular expression support to the T-SQL language. The five new functions give database developers powerful text processing capabilities without leaving T-SQL.

Key takeaways:

  • SQL Server 2025 uses the ICU regex engine with Unicode property support
  • Watch out for greedy vs. lazy quantifier behavior
  • Use \p{...} for Unicode-aware international data

Wrapping Up

To learn more, go here:
https://csmore.info/on/regex


And for help, contact me:
feasel@catallaxyservices.com | @feaselkl


Catallaxy Services consulting:
https://CSmore.info/on/contact