LIKE pattern matching in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find names starting with 'Jo' using LIKE

Given a table users with a column name, which query returns all names starting with 'Jo'?

MySQL

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

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

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

C SELECT name FROM users WHERE name LIKE '_Jo%';

D SELECT name FROM users WHERE name LIKE 'Jo_'

💡 Hint

Use % to match any sequence of characters after 'Jo'.

The pattern 'Jo%' matches any string starting with 'Jo'. '%Jo' matches strings ending with 'Jo'. '_Jo%' requires one character before 'Jo'. 'Jo_' matches strings starting with 'Jo' and exactly one more character.

❓ query_result

intermediate

Match emails ending with '.com'

Which query returns all emails ending with '.com' from the contacts table?

MySQL

SELECT email FROM contacts WHERE email LIKE '%.com';

A SELECT email FROM contacts WHERE email LIKE '%.com';

B SELECT email FROM contacts WHERE email LIKE '%com.';

C SELECT email FROM contacts WHERE email LIKE '.com%';

D SELECT email FROM contacts WHERE email LIKE '%com';

💡 Hint

Use % before '.com' to match any characters before the ending.

The pattern '%.com' matches any string ending with '.com'. '.com%' matches strings starting with '.com'. '%com.' matches strings ending with 'com.'. '%com' matches strings ending with 'com' but not necessarily with a dot.

📝 Syntax

advanced

Identify the syntax error in LIKE pattern

Which option contains a syntax error in the LIKE pattern?

MySQL

SELECT * FROM products WHERE code LIKE 'A_%%';

A SELECT * FROM products WHERE code LIKE 'A_%';

B ;'%_A' EKIL edoc EREHW stcudorp MORF * TCELES

C ELECT * FROM products WHERE code LIKE 'A_%';

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

💡 Hint

Check if the pattern uses valid wildcard characters.

The pattern 'A_%%' is invalid because '%%' is not a valid wildcard sequence. '%' is the wildcard for any sequence of characters, and '_' is for a single character. Double % is redundant and causes syntax error.

❓ query_result

advanced

Find rows where name contains 'an' anywhere

Which query returns all rows from employees where the name contains the substring 'an' anywhere?

MySQL

SELECT * FROM employees WHERE name LIKE '%an%';

A SELECT * FROM employees WHERE name LIKE 'an%';

B SELECT * FROM employees WHERE name LIKE '%an%';

C SELECT * FROM employees WHERE name LIKE '_an%';

D SELECT * FROM employees WHERE name LIKE '%an';

💡 Hint

Use % before and after 'an' to match anywhere in the string.

'%an%' matches any string containing 'an' anywhere. 'an%' matches strings starting with 'an'. '_an%' matches strings with any single character before 'an'. '%an' matches strings ending with 'an'.

🧠 Conceptual

expert

Understanding escape characters in LIKE patterns

You want to find rows where the column code contains the literal underscore character '_'. Which query correctly finds these rows?

A SELECT * FROM table WHERE code LIKE '%[_]%';

B SELECT * FROM table WHERE code LIKE '%_%';

C SELECT * FROM table WHERE code LIKE '%\_%' ESCAPE '\';

D SELECT * FROM table WHERE code LIKE '%\_%';

💡 Hint

Use ESCAPE clause to treat underscore as a normal character.

In LIKE, underscore '_' is a wildcard for any single character. To match a literal underscore, you must escape it. Option C uses ESCAPE '\' to treat '\_' as a literal underscore. Option C tries to escape but lacks ESCAPE clause, so it won't work. Option C matches any single character. Option C is invalid syntax in MySQL.