Pattern matching with LIKE and ILIKE in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Case-sensitive pattern matching with LIKE

Given a table employees with a column name, what will be the output of this query?

SELECT name FROM employees WHERE name LIKE 'Jo%';

Assume the table contains names: 'John', 'john', 'Joanna', 'joey', 'Joseph'.

PostgreSQL

SELECT name FROM employees WHERE name LIKE 'Jo%';

A John, Joanna, Joseph

B john, joey

C John, john, Joanna, joey, Joseph

D No rows returned

💡 Hint

LIKE is case-sensitive in PostgreSQL.

The LIKE operator matches patterns with case sensitivity. Only names starting with uppercase 'Jo' match: 'John', 'Joanna', 'Joseph'.

❓ query_result

intermediate

Case-insensitive pattern matching with ILIKE

Using the same employees table, what will this query return?

SELECT name FROM employees WHERE name ILIKE 'jo%';

Assume the same names as before.

PostgreSQL

SELECT name FROM employees WHERE name ILIKE 'jo%';

A John, Joanna, Joseph

B john, joey

C No rows returned

D John, john, Joanna, joey, Joseph

💡 Hint

ILIKE ignores case differences.

ILIKE matches patterns ignoring case, so all names starting with 'jo' or 'Jo' are returned.

📝 Syntax

advanced

Identify the syntax error in pattern matching

Which option contains a syntax error in the pattern matching query?

A SELECT * FROM products WHERE code LIKE 'B%_2';

B SELECT * FROM products WHERE code LIKE 'C%_';

C SELECT * FROM products WHERE code LIKE 'D%_2' ESCAPE '\';

D SELECT * FROM products WHERE code LIKE 'A_1%';

💡 Hint

Look for unbalanced quotes in the SQL statements.

Option B is missing the closing single quote for the LIKE pattern string 'C%_', causing a syntax error (unclosed string literal). The other options have properly closed strings.

❓ optimization

advanced

Optimizing pattern matching queries

Which query is the most efficient to find names starting with 'Ann' in a large users table?

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

B SELECT * FROM users WHERE name ILIKE 'ann%';

C SELECT * FROM users WHERE LOWER(name) LIKE 'ann%';

D SELECT * FROM users WHERE name LIKE '%Ann%';

💡 Hint

Consider how indexes work with pattern matching.

LIKE with a fixed prefix (no leading wildcard) can use indexes efficiently. ILIKE and LOWER(name) prevent index use. Leading wildcard disables index use.

🧠 Conceptual

expert

Understanding ESCAPE clause in LIKE patterns

Which query correctly searches for names containing the literal underscore character '_' in a customers table?

A SELECT * FROM customers WHERE name LIKE '%[_]%';

B SELECT * FROM customers WHERE name LIKE '%\_%';

C SELECT * FROM customers WHERE name LIKE '%\_%' ESCAPE '\';

D SELECT * FROM customers WHERE name LIKE '%_%';

💡 Hint

Underscore is a wildcard for one character unless escaped.

Option C uses ESCAPE clause to treat backslash as escape character, so '\_' matches literal underscore. Option C lacks ESCAPE clause, so '\' is treated literally, causing no match. Option C matches any single character, not underscore. Option C uses brackets which is not standard in PostgreSQL LIKE.

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