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.

Practice

1. Which of the following statements about LIKE and ILIKE in PostgreSQL is true?

easy

A. LIKE is case-sensitive, while ILIKE is case-insensitive.

B. LIKE ignores case, but ILIKE is case-sensitive.

C. LIKE and ILIKE both ignore case.

D. ILIKE is used only for numeric pattern matching.

Solution

1. Step 1: Understand the difference between LIKE and ILIKE

   LIKE matches patterns considering case, so 'A' and 'a' are different. ILIKE ignores case differences.

2. Step 2: Evaluate the options

   LIKE is case-sensitive, while ILIKE is case-insensitive correctly states the case sensitivity difference. The other options are incorrect based on PostgreSQL behavior.

3. Final Answer:

   LIKE is case-sensitive, while ILIKE is case-insensitive. -> Option A

4. Quick Check:

   LIKE case-sensitive = A [OK]

Hint: Remember: LIKE cares about case, ILIKE ignores it [OK]

Common Mistakes:

• Confusing LIKE and ILIKE case sensitivity
• Thinking ILIKE works in all SQL databases
• Assuming LIKE ignores case by default

2. Which of the following is the correct syntax to find rows where the column name starts with 'Jo' using case-insensitive matching?

easy

A. SELECT * FROM users WHERE name LIKE 'Jo%';

B. SELECT * FROM users WHERE name LIKE '%Jo';

C. SELECT * FROM users WHERE name ILIKE 'Jo%';

D. SELECT * FROM users WHERE name ILIKE '%Jo';

Solution

1. Step 1: Identify the pattern for names starting with 'Jo'

   The pattern should be 'Jo%' to match strings starting with 'Jo'.

2. Step 2: Choose case-insensitive matching syntax

   Use ILIKE for case-insensitive matching in PostgreSQL. SELECT * FROM users WHERE name ILIKE 'Jo%'; uses ILIKE 'Jo%', which is correct.

3. Final Answer:

   SELECT * FROM users WHERE name ILIKE 'Jo%'; -> Option C

4. Quick Check:

   ILIKE + 'Jo%' = C [OK]

Hint: Use ILIKE with 'pattern%' to match start ignoring case [OK]

Common Mistakes:

• Using LIKE instead of ILIKE for case-insensitive search
• Using '%Jo' which matches end, not start
• Forgetting the % wildcard

3. Given a table products with a column code, what will the following query return?

SELECT code FROM products WHERE code LIKE '_A%';

Assuming the code values are: '1A23', 'BA45', 'CA67', 'AA89', 'AB12'.

medium

A. AA89, AB12

B. 1A23, AA89

C. BA45, CA67, AB12

D. 1A23, BA45, CA67, AA89

Solution

1. Step 1: Understand the pattern '_A%'

   The underscore (_) matches exactly one character, 'A' is literal, and % matches any sequence after. So the second character must be 'A'.

2. Step 2: Check each code value

   '1A23' (second char 'A') matches. 'BA45' (second 'A') matches. 'CA67' (second 'A') matches. 'AA89' (second 'A') matches. 'AB12' (second 'B') does not match.

3. Final Answer:

   1A23, BA45, CA67, AA89 -> Option D

4. Quick Check:

   Second char 'A' = D [OK]

Hint: Underscore matches one char; check second char carefully [OK]

Common Mistakes:

• Confusing _ with %
• Ignoring position of characters in pattern
• Missing some matching rows

4. You wrote this query to find all users whose email ends with '@example.com' ignoring case:

SELECT * FROM users WHERE email LIKE '[e-mail]';

But it misses emails like '[e-mail]'. What is the fix?

medium

A. Change LIKE to ILIKE: email ILIKE '[e-mail]'

B. All of the above

C. Use UPPER function: UPPER(email) LIKE '[e-mail]'

D. Add LOWER function: LOWER(email) LIKE '[e-mail]'

Solution

1. Step 1: Identify why LIKE misses case differences

   LIKE is case-sensitive, so '[e-mail]' does not match '[e-mail]'.

2. Step 2: Explore fixes for case-insensitive matching

   Change LIKE to ILIKE: email ILIKE '[e-mail]' uses ILIKE which ignores case. Add LOWER function: LOWER(email) LIKE '[e-mail]' converts email to lowercase before matching. Use UPPER function: UPPER(email) LIKE '[e-mail]' converts email to uppercase and matches uppercase pattern. All achieve case-insensitive matching.

3. Step 3: Confirm all options work

   All three approaches fix the problem, so the correct answer is all of the above.

4. Final Answer:

   All of the above -> Option B

5. Quick Check:

   Case-insensitive fix = B [OK]

Hint: Use ILIKE or LOWER/UPPER for case-insensitive matching [OK]

Common Mistakes:

• Using LIKE without case conversion
• Forgetting to match case in pattern
• Assuming LIKE is case-insensitive

5. You want to find all product codes in a table inventory where the code contains exactly three letters followed by any digits, ignoring case. Which query correctly uses pattern matching?

hard

A. SELECT * FROM inventory WHERE code ~* '^[a-zA-Z]{3}[0-9]+$';

B. SELECT * FROM inventory WHERE code ILIKE '___%';

C. SELECT * FROM inventory WHERE code LIKE '___%';

D. SELECT * FROM inventory WHERE code ILIKE '???%';

Solution

1. Step 1: Understand the pattern requirements

   We need exactly three letters (any case) followed by digits, and the match should be case-insensitive.

2. Step 2: Evaluate pattern matching options

   LIKE '___%' and ILIKE '___%' match any three characters followed by anything, but do not restrict to letters followed by digits. ILIKE '???%' uses invalid wildcard syntax (? is not supported in SQL LIKE/ILIKE). The ~* '^[a-zA-Z]{3}[0-9]+$' uses regex with case-insensitive flag to match exactly three letters then one or more digits to end of string, which fits perfectly.

3. Step 3: Confirm correct syntax and behavior

   ~* is PostgreSQL's case-insensitive regex match operator with correct pattern.

4. Final Answer:

   SELECT * FROM inventory WHERE code ~* '^[a-zA-Z]{3}[0-9]+$'; -> Option A

5. Quick Check:

   Regex with ~* matches exact pattern = A [OK]

Hint: Use regex (~*) for exact letter-digit patterns ignoring case [OK]

Common Mistakes:

• Using LIKE with wildcards but no character restrictions
• Using invalid wildcard characters like '?'
• Not using case-insensitive matching properly