Regular expression matching (~ operator) in PostgreSQL - Step-by-Step Execution

Concept Flow - Regular expression matching (~ operator)

Start with input string and pattern

↓

Apply ~ operator: Does string match regex pattern?

↓

Return TRUE

↓

End

The ~ operator checks if a string matches a regular expression pattern and returns TRUE or FALSE.

Execution Sample

PostgreSQL

SELECT 'apple' ~ '^a.*e$' AS match_result;

Checks if 'apple' starts with 'a' and ends with 'e' using regex.

Execution Table

Step	Input String	Regex Pattern	Match Result	Explanation
1	'apple'	'^a.*e$'	TRUE	'apple' starts with 'a' and ends with 'e', matches pattern
2	'banana'	'^a.*e$'	FALSE	'banana' does not start with 'a' and end with 'e', no match
3	'ace'	'^a.*e$'	TRUE	'ace' starts with 'a' and ends with 'e', matches pattern
4	'ape'	'^a.*e$'	TRUE	'ape' starts with 'a' and ends with 'e', matches pattern
5	'app'	'^a.*e$'	FALSE	'app' does not end with 'e', no match
6	N/A	N/A	N/A	Execution ends after checking all inputs

💡 All input strings checked against the regex pattern; execution stops.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
Input String	'apple'	'banana'	'ace'	'ape'	'app'	N/A	N/A
Regex Pattern	'^a.*e$'	'^a.*e$'	'^a.*e$'	'^a.*e$'	'^a.*e$'	N/A	N/A
Match Result	TRUE	FALSE	TRUE	TRUE	FALSE	N/A	N/A

Key Moments - 2 Insights

Why does 'banana' return FALSE even though it contains 'a' and 'e'?

Because the regex '^a.*e$' requires the string to start with 'a' and end with 'e'. 'banana' starts with 'b', so it does not match (see execution_table row 2).

What does the '^' and '$' mean in the regex pattern?

'^' means the string must start with the following pattern, and '$' means it must end with the preceding pattern. This ensures the entire string matches the pattern exactly (see execution_table rows 1 and 3).

Visual Quiz - 3 Questions

Look at the execution_table, what is the match result for the input string 'ape'?

A FALSE

B NULL

C TRUE

D Error

💡 Hint

Check execution_table row 4 under 'Match Result' column.

At which step does the input string fail to match the regex pattern?

A Step 1

B Step 2

C Step 4

D Step 3

💡 Hint

Look for 'FALSE' in the 'Match Result' column in execution_table.

If the regex pattern was changed to '^a.*', which input string would still return FALSE?

A 'banana'

B 'app'

C 'apple'

D 'ape'

💡 Hint

Consider that '^a.*' means string must start with 'a' followed by anything; check variable_tracker for input strings.

Concept Snapshot

Regular expression matching with ~ operator:
- Syntax: string ~ 'pattern'
- Returns TRUE if string matches regex pattern, else FALSE
- '^' anchors start, '$' anchors end
- Useful for pattern checks in text data
- Case sensitive by default

Full Transcript

This visual execution shows how the PostgreSQL ~ operator checks if a string matches a regular expression pattern. The flow starts with an input string and a regex pattern. The operator tests if the string fits the pattern, returning TRUE if it does and FALSE if not. For example, 'apple' matches '^a.*e$' because it starts with 'a' and ends with 'e'. 'banana' does not match because it starts with 'b'. The execution table traces multiple strings against the pattern, showing results step-by-step. Key points include understanding the meaning of '^' and '$' in regex and why some strings fail to match. The quiz questions help reinforce these concepts by asking about specific steps and outcomes in the execution. This helps beginners see exactly how regex matching works in PostgreSQL.