Regular expression functions (regexp_match, regexp_replace) in PostgreSQL - Step-by-Step Execution

Concept Flow - Regular expression functions (regexp_match, regexp_replace)

Input String

↓

Apply regexp_match

↓

Return first match array or NULL

↓

Apply regexp_replace

↓

Return string with replacements

↓

Output Result

The input string is processed by regexp_match to find matching parts, returning an array or NULL. Then regexp_replace substitutes matching parts with a new string, returning the modified string.

Execution Sample

PostgreSQL

SELECT regexp_match('abc123xyz', '\\d+');
SELECT regexp_replace('abc123xyz', '\\d+', 'NUM');

Find digits in 'abc123xyz' and replace them with 'NUM'.

Execution Table

Step	Function	Input String	Pattern	Action	Result
1	regexp_match	abc123xyz	\d+	Find first digit sequence	{123}
2	regexp_replace	abc123xyz	\d+	Replace digit sequence with 'NUM'	abcNUMxyz
3	End			No more steps	Execution complete

💡 All functions applied, results returned.

Variable Tracker

Variable	Start	After regexp_match	After regexp_replace	Final
input_string	abc123xyz	abc123xyz	abc123xyz	abc123xyz
match_result	NULL	{123}	{123}	{123}
replace_result	NULL	NULL	abcNUMxyz	abcNUMxyz

Key Moments - 3 Insights

Why does regexp_match return an array instead of a string?

regexp_match returns an array because it can capture multiple groups in the pattern. In the execution_table row 1, the result is {123}, an array with one element, the matched digits.

What happens if regexp_match finds no match?

If no match is found, regexp_match returns NULL. This is shown in the variable_tracker where match_result starts as NULL before matching.

Does regexp_replace change the original string?

No, regexp_replace returns a new string with replacements. The original input_string remains unchanged as shown in variable_tracker.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of regexp_match at step 1?

A NULL

B 123

C {123}

D 'abc123xyz'

💡 Hint

Check the 'Result' column in execution_table row 1.

At which step does the string get changed by replacing digits?

A Step 1

B Step 2

C Step 3

D No step changes the string

💡 Hint

Look at the 'Action' and 'Result' columns in execution_table row 2.

If the input string had no digits, what would regexp_match return?

A NULL

B The original string

C An empty array {}

D An error

💡 Hint

Refer to key_moments explanation about no match behavior.

Concept Snapshot

regexp_match(string, pattern) returns an array of the first match or NULL if none.
regexp_replace(string, pattern, replacement) returns a new string with matches replaced.
Patterns use regular expressions.
Original string is not changed.
Useful for extracting or modifying text based on patterns.

Full Transcript

This visual execution shows how PostgreSQL's regexp_match and regexp_replace functions work. First, regexp_match searches the input string 'abc123xyz' for digits using the pattern '\d+'. It finds '123' and returns it as an array {123}. Then, regexp_replace replaces the digits '123' with 'NUM', producing 'abcNUMxyz'. The original string remains unchanged. If no match is found, regexp_match returns NULL. These functions help find and modify text using patterns.

Practice

1. What does the PostgreSQL function regexp_match('abc123', '\\d+') return?

easy

A. An error because of incorrect syntax

B. The string 'abc123' unchanged

C. An empty array because no digits are found

D. An array with the first sequence of digits found: ['123']

Solution

1. Step 1: Understand regexp_match function

   The function searches the input string for the first substring matching the pattern.

2. Step 2: Apply pattern '\\d+' to 'abc123'

   The pattern '\\d+' matches one or more digits. In 'abc123', '123' matches.

3. Final Answer:

   An array with the first sequence of digits found: ['123'] -> Option D

4. Quick Check:

   regexp_match returns first match array [OK]

Hint: regexp_match returns first matching group as array [OK]

Common Mistakes:

• Thinking regexp_match returns the whole string
• Expecting a string instead of an array
• Confusing regexp_match with regexp_replace

2. Which of the following is the correct syntax to replace all digits in 'abc123def' with '#' using regexp_replace in PostgreSQL?

easy

A. regexp_replace('abc123def', '\\d+', '#', 'g')

B. regexp_replace('abc123def', '[0-9]', '#', 'g')

C. regexp_replace('abc123def', '[0-9]+', '#')

D. regexp_replace('abc123def', '\\d', '#')

Solution

1. Step 1: Understand regexp_replace parameters

   regexp_replace(text, pattern, replacement, flags) replaces matching parts. To replace all matches, use flag 'g'.

2. Step 2: Choose pattern and flags to replace all digits

   Pattern '\\d+' matches one or more digits. Flag 'g' ensures all digit sequences replaced, not just first.

3. Final Answer:

   regexp_replace('abc123def', '\\d+', '#', 'g') -> Option A

4. Quick Check:

   Use 'g' flag to replace all matches [OK]

Hint: Use 'g' flag in regexp_replace to replace all matches [OK]

Common Mistakes:

• Omitting 'g' flag replaces only first match
• Using '\\d' without 'g' replaces only one digit
• Confusing pattern syntax with wrong brackets

3. What is the result of this query?

SELECT regexp_match('user_2023_data', 'user_(\\d{4})_data');

medium

A. NULL

B. ['user_2023_data']

C. ['2023']

D. An error due to wrong pattern

Solution

1. Step 1: Analyze the pattern 'user_(\\d{4})_data'

   The pattern looks for 'user_' followed by exactly 4 digits (captured), then '_data'.

2. Step 2: Match pattern against 'user_2023_data'

   The string matches perfectly, capturing '2023' as the group.

3. Final Answer:

   ['2023'] -> Option C

4. Quick Check:

   Captured group returns ['2023'] [OK]

Hint: Captured groups in regexp_match return as array [OK]

Common Mistakes:

• Expecting full string instead of captured group
• Not escaping backslashes properly
• Confusing regexp_match with regexp_replace output

4. Identify the error in this query:

SELECT regexp_replace('abc123def', '\\d+', '#');

It is intended to replace all digit sequences with '#'.

medium

A. regexp_replace cannot replace digits

B. Missing 'g' flag, so only first digit sequence replaced

C. Replacement '#' must be in double quotes

D. Pattern '\\d+' is invalid syntax

Solution

1. Step 1: Understand regexp_replace behavior without flags

   Without 'g' flag, regexp_replace replaces only the first match.

2. Step 2: Check the pattern and replacement

   Pattern '\\d+' is valid and replacement '#' is correct. Only missing 'g' flag causes partial replacement.

3. Final Answer:

   Missing 'g' flag, so only first digit sequence replaced -> Option B

4. Quick Check:

   Use 'g' flag to replace all matches [OK]

Hint: Add 'g' flag to replace all matches in regexp_replace [OK]

Common Mistakes:

• Assuming all matches replaced without 'g'
• Thinking pattern syntax is wrong
• Using single quotes incorrectly for replacement

5. You want to extract the domain name from email addresses like '[e-mail]' using regexp_match. Which query correctly extracts 'example'?

hard

A. SELECT (regexp_match('[e-mail]', '@([\\w]+)\\.com'))[1];

B. SELECT regexp_match('[e-mail]', '@([\\w]+)\\.com');

C. SELECT regexp_replace('[e-mail]', '.*@([\\w]+)\\.com', '\\1');

D. SELECT regexp_replace('[e-mail]', '@([\\w]+)\\.com', '\\1');

Solution

1. Step 1: Understand regexp_match returns array of groups

   regexp_match returns an array with captured groups. To get the domain, access first element [1].

2. Step 2: Check pattern '@([\\w]+)\\.com'

   This pattern captures word characters after '@' and before '.com', which is the domain.

3. Final Answer:

   SELECT (regexp_match('[e-mail]', '@([\\w]+)\\.com'))[1]; -> Option A

4. Quick Check:

   Access first group from regexp_match array [OK]

Hint: Use (regexp_match(...))[1] to get captured group string [OK]

Common Mistakes:

• Not accessing array element from regexp_match
• Using regexp_replace without global flag incorrectly
• Confusing pattern syntax for domain extraction