This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Write advanced T-SQL code
      --> Write queries that include regular expressions, such as REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT, REGEXP_MATCHES, and REGEXP_SPLIT_TO_TABLE

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Regular expressions (regex) are powerful pattern-matching expressions used to search, validate, extract, replace, and manipulate text. They have been available in many programming languages for years and are now available in SQL Server 2025 (17.x) Preview and Azure SQL Database through native T-SQL regular expression functions.

For developers, regex significantly simplifies many text-processing tasks that previously required combinations of LIKE, PATINDEX, CHARINDEX, SUBSTRING, REPLACE, and custom T-SQL logic.

For the DP-800: Developing AI-Enabled Database Solutions exam, understanding these functions is increasingly important because AI-enabled applications frequently process:

User prompts
Chat conversations
Log files
Emails
Product descriptions
Documents
JSON data
Metadata
Search indexes

Regular expressions allow SQL Server to efficiently validate, search, and transform this semi-structured text.

What is a Regular Expression?

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

For example:

Pattern	Meaning
`\d`	Any digit
`[A-Z]`	Uppercase letter
`[a-z]`	Lowercase letter
`[A-Za-z]`	Any letter
`.`	Any character
`.*`	Zero or more characters
`+`	One or more occurrences
`?`	Optional occurrence
`^`	Beginning of string
`$`	End of string
`\s`	Whitespace
`\w`	Word character
`[^0-9]`	Anything except digits

Example:

^\d{5}$

Matches exactly five digits.

Examples:

			
12345 ✔
98765 ✔
1234 ✘
123456 ✘
ABCDE ✘

		

SQL Server Regular Expression Functions

The newest T-SQL regular expression functions include:

REGEXP_LIKE()
REGEXP_REPLACE()
REGEXP_SUBSTR()
REGEXP_INSTR()
REGEXP_COUNT()
REGEXP_MATCHES()
REGEXP_SPLIT_TO_TABLE()

Each function serves a different purpose.

REGEXP_LIKE()

Purpose

Tests whether text matches a regular expression.

Syntax

REGEXP_LIKE(expression, pattern)

Example

			
SELECT CustomerEmail
FROM Customers
WHERE REGEXP_LIKE(
    CustomerEmail,
    '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
);

		

This returns only rows containing valid email addresses.

Common Uses

Validate email addresses
Validate ZIP codes
Validate phone numbers
Validate product codes
Validate license numbers
Check AI-generated output

REGEXP_REPLACE()

Purpose

Replaces matching text.

Syntax

REGEXP_REPLACE(expression, pattern, replacement)

Example

Remove non-numeric characters from a phone number.

			
SELECT REGEXP_REPLACE(
'(555) 123-4567',
'[^0-9]',
''
);

		

Output

5551234567

Example

Replace multiple spaces with one space.

			
SELECT REGEXP_REPLACE(
'John     Smith',
'\s+',
' '
);

		

Output

John Smith

Common Uses

Data cleansing
Standardization
Removing punctuation
Removing HTML tags
Cleaning AI responses

REGEXP_SUBSTR()

Purpose

Returns the first substring that matches a pattern.

Syntax

REGEXP_SUBSTR(expression, pattern)

Example

			
SELECT REGEXP_SUBSTR(
'Invoice #INV-2025-1045',
'INV-[0-9-]+'
);

Output

INV-2025-1045

Useful for extracting:

Invoice numbers
Tracking numbers
Product IDs
URLs
Dates

REGEXP_INSTR()

Purpose

Returns the starting position of a pattern.

Syntax

REGEXP_INSTR(expression, pattern)

Example

			
SELECT REGEXP_INSTR(
'Customer ID: 12345',
'\d+'
);

Output

If no match exists, the function returns 0.

REGEXP_COUNT()

Purpose

Counts how many times a pattern occurs.

Syntax

REGEXP_COUNT(expression, pattern)

Example

			
SELECT REGEXP_COUNT(
'cat dog cat bird cat',
'cat'
);

Output

Useful for:

Counting hashtags
Counting keywords
Counting repeated words
Measuring AI response quality

REGEXP_MATCHES()

Purpose

Returns all substrings that match a pattern.

Unlike REGEXP_SUBSTR(), which returns only the first match, REGEXP_MATCHES() returns every match.

Example

			
SELECT *
FROM REGEXP_MATCHES(
'Phone: 555-1111 Office: 555-2222',
'\d{3}-\d{4}'
);

		

Output

			
555-1111
555-2222

Common uses include:

Finding all phone numbers
Extracting URLs
Extracting hashtags
Finding dates

REGEXP_SPLIT_TO_TABLE()

Purpose

Splits text into rows using a regular expression delimiter.

Example

			
SELECT *
FROM REGEXP_SPLIT_TO_TABLE(
'SQL,Azure,AI,Python',
','
);

		

Output

Value
SQL
Azure
AI
Python

Example

Split on one or more spaces.

			
SELECT *
FROM REGEXP_SPLIT_TO_TABLE(
'SQL   Azure   AI',
'\s+'
);

		

Comparing the Functions

Function	Purpose
REGEXP_LIKE()	Test whether text matches a pattern
REGEXP_REPLACE()	Replace matching text
REGEXP_SUBSTR()	Return first matching substring
REGEXP_INSTR()	Return position of first match
REGEXP_COUNT()	Count matches
REGEXP_MATCHES()	Return all matches
REGEXP_SPLIT_TO_TABLE()	Split text into rows

Common Regular Expression Patterns

Email

^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$

US ZIP Code

^\d{5}$

ZIP+4

^\d{5}-\d{4}$

Phone Number

^\(?\d{3}\)?[- ]?\d{3}[- ]?\d{4}$

GUID

^[0-9A-Fa-f-]{36}$

URL

https?://.*

Integer

^\d+$

Decimal Number

^\d+\.\d+$

AI-Enabled Database Scenarios

Regular expressions are especially valuable when AI applications generate or consume semi-structured text.

Examples include:

Validating AI-generated email addresses
Extracting invoice numbers from chatbot responses
Cleaning OCR text
Removing HTML from generated content
Parsing metadata
Detecting URLs in AI responses
Validating JSON fragments
Finding sensitive information before storage
Identifying product codes
Processing vector search metadata

Performance Considerations

Regular expressions are more computationally expensive than simple string comparisons.

To improve performance:

Use simple patterns whenever possible.
Filter rows before applying regex functions.
Avoid leading wildcards when simpler predicates suffice.
Avoid unnecessarily complex nested expressions.
Consider computed columns for frequently evaluated values.
Benchmark regex queries on large datasets.
Use indexes to reduce the number of rows that require regex evaluation.

Best Practices

Keep patterns simple and readable.
Test regex thoroughly using representative data.
Escape special characters when needed.
Validate user-supplied patterns to prevent errors.
Use anchors (^ and $) when matching an entire string.
Use character classes instead of long OR conditions.
Prefer regex only when simpler string functions cannot meet the requirement.
Document complex expressions for maintainability.
Handle NULL values appropriately.
Monitor performance on large datasets.

Common Exam Tips

For the DP-800 exam, remember:

REGEXP_LIKE() validates or filters text.
REGEXP_REPLACE() modifies text.
REGEXP_SUBSTR() extracts the first match.
REGEXP_INSTR() returns the position of a match.
REGEXP_COUNT() counts pattern occurrences.
REGEXP_MATCHES() returns all matches.
REGEXP_SPLIT_TO_TABLE() converts delimited text into rows.
Regular expressions are ideal for processing semi-structured text used by AI-enabled applications.
Regex offers significantly more flexibility than LIKE and PATINDEX for complex pattern matching.

Practice Exam Questions

Question 1

A developer needs to validate that a column contains only properly formatted email addresses. Which function should be used?

A. REGEXP_REPLACE()

B. REGEXP_LIKE()

C. REGEXP_SUBSTR()

D. REGEXP_COUNT()

Answer: B

Explanation: REGEXP_LIKE() evaluates whether a string matches a regular expression and is the appropriate function for validating email formats.

Question 2

You need to remove all punctuation from customer phone numbers before storing them. Which function is most appropriate?

A. REGEXP_REPLACE()

B. REGEXP_INSTR()

C. REGEXP_MATCHES()

D. REGEXP_COUNT()

Answer: A

Explanation: REGEXP_REPLACE() replaces matching characters or patterns, making it ideal for removing punctuation or formatting characters.

Question 3

A product description contains multiple serial numbers, and you need to return every matching serial number. Which function should you use?

A. REGEXP_SUBSTR()

B. REGEXP_COUNT()

C. REGEXP_MATCHES()

D. REGEXP_LIKE()

Answer: C

Explanation: REGEXP_MATCHES() returns all occurrences that satisfy the specified regular expression rather than just the first match.

Question 4

You need to extract the first invoice number from a block of text. Which function is the best choice?

A. REGEXP_SPLIT_TO_TABLE()

B. REGEXP_INSTR()

C. REGEXP_SUBSTR()

D. REGEXP_REPLACE()

Answer: C

Explanation: REGEXP_SUBSTR() extracts and returns the first substring that matches the specified regular expression.

Question 5

Which function returns the character position where the first match begins?

A. REGEXP_INSTR()

B. REGEXP_COUNT()

C. REGEXP_MATCHES()

D. REGEXP_REPLACE()

Answer: A

Explanation: REGEXP_INSTR() returns the starting position of the first occurrence of a pattern within a string.

Question 6

A developer needs to determine how many times the word “error” appears in a log entry. Which function should be used?

A. REGEXP_MATCHES()

B. REGEXP_COUNT()

C. REGEXP_SUBSTR()

D. REGEXP_LIKE()

Answer: B

Explanation: REGEXP_COUNT() counts the number of occurrences of a pattern within a string.

Question 7

A comma-separated list stored in a column must be converted into one row per value. Which function is designed for this task?

A. REGEXP_REPLACE()

B. REGEXP_INSTR()

C. REGEXP_SPLIT_TO_TABLE()

D. REGEXP_SUBSTR()

Answer: C

Explanation: REGEXP_SPLIT_TO_TABLE() divides a string into multiple rows using a regular expression as the delimiter.

Question 8

Which regular expression pattern matches exactly five digits?

A. \d+

B. ^\d{5}$

C. \d{5,}

D. [0-9]*

Answer: B

Explanation: ^\d{5}$ anchors the match to the beginning and end of the string and requires exactly five digits.

Question 9

Why are regular expressions particularly valuable in AI-enabled database solutions?

A. They automatically train AI models.

B. They replace JSON processing.

C. They eliminate the need for SQL indexes.

D. They efficiently validate, extract, and transform semi-structured text generated by AI systems.

Answer: D

Explanation: AI applications frequently exchange semi-structured text, and regex functions simplify validation, extraction, cleansing, and transformation directly within SQL.

Question 10

When should you prefer regular expressions over simple string functions such as LIKE?

A. For every text comparison.

B. Only when searching numeric columns.

C. When complex pattern matching or text extraction is required.

D. Only when working with JSON data.

Answer: C

Explanation: Regular expressions are best suited for sophisticated pattern matching, validation, and extraction tasks that cannot be easily implemented using simpler string functions.

Go to the DP-800 Exam Prep Hub main page

Introduction

What is a Regular Expression?

SQL Server Regular Expression Functions

REGEXP_LIKE()

Purpose

Common Uses

REGEXP_REPLACE()

Purpose

Common Uses

REGEXP_SUBSTR()

Purpose

REGEXP_INSTR()

Purpose

REGEXP_COUNT()

Purpose

REGEXP_MATCHES()

Purpose

REGEXP_SPLIT_TO_TABLE()

Purpose

Comparing the Functions

Common Regular Expression Patterns

Email

US ZIP Code

ZIP+4

Phone Number

GUID

URL

Integer

Decimal Number

AI-Enabled Database Scenarios

Performance Considerations

Best Practices

Common Exam Tips

Practice Exam Questions

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Information and resources for the data professionals' community