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:

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

14

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

3

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

Example

Split on one or more spaces.

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

Comparing the Functions

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