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.