Pattern matching with LIKE and ILIKE in PostgreSQL - Step-by-Step Execution

Concept Flow - Pattern matching with LIKE and ILIKE

Start Query

↓

Check Pattern

↓

Use LIKE or ILIKE

↓

Match Rows

↓

Return Matched Rows

↓

End Query

The query starts, checks the pattern condition using LIKE or ILIKE, matches rows accordingly, and returns those rows.

Execution Sample

PostgreSQL

SELECT name FROM users WHERE name LIKE 'A%';

This query finds all users whose names start with 'A' using case-sensitive pattern matching.

Execution Table

Step	Row name	Pattern	LIKE Match?	ILIKE Match?	Action
1	Alice	'A%'	True	True	Include row
2	alice	'A%'	False	True	Include row only for ILIKE
3	Bob	'A%'	False	False	Exclude row
4	Amanda	'A%'	True	True	Include row
5	ann	'A%'	False	True	Include row only for ILIKE
6	Albert	'A%'	True	True	Include row
7	brian	'A%'	False	False	Exclude row
Exit	-	-	-	-	No more rows to check

💡 All rows checked, query ends returning matched rows.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	After Row 6	After Row 7	Final
Current Row	None	Alice	alice	Bob	Amanda	ann	Albert	brian	None
LIKE Match	N/A	True	False	False	True	False	True	False	N/A
ILIKE Match	N/A	True	True	False	True	True	True	False	N/A
Rows Included (LIKE)	[]	[Alice]	[Alice]	[Alice]	[Alice, Amanda]	[Alice, Amanda]	[Alice, Amanda, Albert]	[Alice, Amanda, Albert]	[Alice, Amanda, Albert]
Rows Included (ILIKE)	[]	[Alice]	[Alice, alice]	[Alice, alice]	[Alice, alice, Amanda]	[Alice, alice, Amanda, ann]	[Alice, alice, Amanda, ann, Albert]	[Alice, alice, Amanda, ann, Albert]	[Alice, alice, Amanda, ann, Albert]

Key Moments - 3 Insights

Why does 'alice' not match with LIKE 'A%' but matches with ILIKE 'A%'?

LIKE is case-sensitive, so 'alice' starting with lowercase 'a' does not match uppercase 'A'. ILIKE ignores case, so it matches. See execution_table rows 2.

What does the '%' symbol mean in the pattern 'A%'?

'%' means any sequence of characters (including none). So 'A%' matches any string starting with 'A'. This is why 'Alice' and 'Amanda' match. See execution_table rows 1 and 4.

Why are some rows excluded from the result?

Rows like 'Bob' and 'brian' do not start with 'A' or 'a', so they don't match the pattern. Both LIKE and ILIKE return false for them. See execution_table rows 3 and 7.

Visual Quiz - 3 Questions

Look at the execution_table, what is the LIKE match result for 'Albert'?

A Unknown

B True

C False

D Error

💡 Hint

Check row 6 under the LIKE Match? column in execution_table.

At which step does the ILIKE match first become True for a lowercase name?

A Step 1

B Step 3

C Step 2

D Step 5

💡 Hint

Look at the ILIKE Match? column for lowercase names in execution_table.

If the pattern changes to '%a', which rows would LIKE match?

A Names ending with 'a'

B Names starting with 'a'

C Names containing 'a' anywhere

D Names ending with 'A' only

💡 Hint

Remember '%' means any characters, so '%a' means strings ending with 'a'.

Concept Snapshot

Pattern matching in PostgreSQL uses LIKE (case-sensitive) and ILIKE (case-insensitive).
Use '%' for any sequence of characters and '_' for a single character.
LIKE 'A%' matches strings starting with 'A'.
ILIKE ignores case differences.
Useful for filtering text data by patterns.

Full Transcript

This visual execution shows how PostgreSQL uses LIKE and ILIKE to match text patterns. The query checks each row's 'name' against the pattern 'A%'. LIKE matches only names starting with uppercase 'A', while ILIKE matches names starting with 'A' or 'a'. The '%' symbol means any characters can follow. Rows like 'Alice', 'Amanda', and 'Albert' match both LIKE and ILIKE. Rows like 'alice' and 'ann' match only ILIKE because of case insensitivity. Rows like 'Bob' and 'brian' do not match either. This helps understand how pattern matching filters data based on text patterns and case sensitivity.