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.