SQL Server 2025 Regular expressions functions

 

Core Regex Functions

The system provides several functions to search, modify, and analyze strings using patterns:

Function	Purpose
REGEXP_LIKE	Returns True if the text matches the pattern.
REGEXP_REPLACE	Modifies a string by replacing matches with a new value.
REGEXP_SUBSTR	Extracts a specific occurrence of a matching substring.
REGEXP_INSTR	Returns the position (start or end) of a match.
REGEXP_COUNT	Counts how many times a pattern appears.
REGEXP_MATCHES	Returns a table of all captured substrings.
REGEXP_SPLIT_TO_TABLE	Splits a string into a table using the pattern as a delimiter.

Syntax and Construction

Regex patterns are built using literal characters (which match themselves) and metacharacters (which have special logic).

• Escaping: To match a literal metacharacter (like * or ?), you must use a backslash (\*).

• Concatenation & Alternation: * e1 e2 matches "e1" followed by "e2".

  • e1 | e2 matches either "e1" or "e2".

• Repetition Operators:

  • *: Zero or more matches.

  • +: One or more matches.

  • ?: Zero or one match.

• Precedence: Operators follow a specific order of strength: Repetition (strongest) > Concatenation > Alternation (weakest). Parentheses () can be used to override this order.

let's assume we are working with a string representing a standard product code: Item_#1234-Blue.

 Examples

• REGEXP_LIKE (The Filter)

  • Goal: Check if a string contains a 4-digit number.

  • Pattern: \d{4}

  • Result: True (since 1234 matches).

• REGEXP_SUBSTR (The Extractor)

  • Goal: Pull the color name from the end of the string.

  • Pattern: [^-]+$ (Matches characters after the last hyphen).

  • Result: Blue

• REGEXP_REPLACE (The Cleaner)

  • Goal: Remove the hash symbol (#).

  • Pattern: #

  • Replacement: '' (Empty string).

  • Result: Item_1234-Blue

• REGEXP_COUNT (The Auditor)

• Goal: Count how many times a hyphen appears.

• Pattern: -

• Result: 1

Cheers

Samitha