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.

Practice

1. What does the % symbol represent in a MySQL LIKE pattern?

easy

A. Any sequence of zero or more characters

B. Exactly one character

C. A digit from 0 to 9

D. A special character like @ or #

Solution

1. Step 1: Understand the role of % in LIKE

   The % symbol in a LIKE pattern matches any sequence of characters, including an empty sequence.

2. Step 2: Compare with other wildcard symbols

   The _ symbol matches exactly one character, not a sequence. % is for zero or more characters.

3. Final Answer:

   Any sequence of zero or more characters -> Option A

4. Quick Check:

   % means any characters [OK]

Hint: Remember % means many chars, _ means one char [OK]

Common Mistakes:

• Confusing % with _
• Thinking % matches only one character
• Assuming % matches digits only

2. Which of the following is the correct syntax to find names starting with 'Jo' using LIKE in MySQL?

easy

A. WHERE name LIKE 'Jo%'

B. WHERE name LIKE '%Jo'

C. WHERE name LIKE '_Jo%'

D. WHERE name LIKE 'Jo_'

Solution

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

   Names starting with 'Jo' means 'Jo' followed by any characters, so pattern is 'Jo%'.

2. Step 2: Check each option

   WHERE name LIKE 'Jo%' uses 'Jo%' which matches strings starting with 'Jo'. Others do not match this pattern correctly.

3. Final Answer:

   WHERE name LIKE 'Jo%' -> Option A

4. Quick Check:

   Start with 'Jo' = 'Jo%' [OK]

Hint: Use % after prefix to match any ending [OK]

Common Mistakes:

• Putting % before 'Jo' instead of after
• Using _ instead of %
• Misplacing wildcards

3. Given a table users with a column email, what will this query return?

SELECT email FROM users WHERE email LIKE '%@gmail._om';

medium

A. Emails containing '@gmail' anywhere

B. Emails ending exactly with '@gmail._om' string

C. Emails ending with '@gmail.com' or '@gmail.dom' etc. (one char before 'om')

D. Emails starting with '@gmail' and ending with 'om'

Solution

1. Step 1: Analyze the pattern '%@gmail._om'

   The % matches any characters before '@gmail.', then _ matches exactly one character, then 'om' follows.

2. Step 2: Understand what emails match

   Emails ending with '@gmail.com', '@gmail.dom', '@gmail.xom' etc. match because _ matches one character before 'om'.

3. Final Answer:

   Emails ending with '@gmail.com' or '@gmail.dom' etc. (one char before 'om') -> Option C

4. Quick Check:

   _ matches one char before 'om' [OK]

Hint: Use _ for exactly one char, % for many chars [OK]

Common Mistakes:

• Thinking _ matches zero or many characters
• Ignoring the position of _ in pattern
• Assuming % matches only one character

4. You wrote this query but it returns no rows:

SELECT * FROM products WHERE name LIKE 'TV_';

What is the likely problem?

medium

A. The underscore _ is not a valid wildcard in LIKE

B. The pattern should be 'TV%' to match names starting with 'TV'

C. The query should use = instead of LIKE

D. The pattern 'TV_' matches only names with exactly 3 characters starting with 'TV'

Solution

1. Step 1: Understand the pattern 'TV_'

   The pattern 'TV_' matches strings starting with 'TV' and exactly one more character, so total length 3.

2. Step 2: Reason why no rows returned

   If no product names are exactly 3 characters starting with 'TV', no rows match. Longer names like 'TVS' or 'TV42' won't match.

3. Final Answer:

   The pattern 'TV_' matches only names with exactly 3 characters starting with 'TV' -> Option D

4. Quick Check:

   _ means exactly one char [OK]

Hint: Remember _ matches exactly one char, so length matters [OK]

Common Mistakes:

• Thinking _ matches zero or many characters
• Using LIKE incorrectly with =
• Assuming 'TV_' matches longer names

5. You want to find all customers whose phone numbers start with '555' and have exactly 10 digits total. Which query correctly uses LIKE?

hard

A. SELECT * FROM customers WHERE phone LIKE '555________';

B. SELECT * FROM customers WHERE phone LIKE '555_______';

C. SELECT * FROM customers WHERE phone LIKE '555_________';

D. SELECT * FROM customers WHERE phone LIKE '555%';

Solution

1. Step 1: Understand the requirement

   Phone numbers start with '555' and have exactly 10 digits total, so 3 fixed digits + 7 more digits.

2. Step 2: Count underscores needed

   Each underscore _ matches exactly one character. After '555', we need 7 underscores to make total length 10.

3. Step 3: Check options

   SELECT * FROM customers WHERE phone LIKE '555_______'; has '555' plus 7 underscores, matching exactly 10 characters. Others have wrong underscore counts or use % which allows variable length.

4. Final Answer:

   SELECT * FROM customers WHERE phone LIKE '555_______'; -> Option B

5. Quick Check:

   7 underscores after '555' = 10 chars total [OK]

Hint: Count underscores to match exact length after prefix [OK]

Common Mistakes:

• Using % which allows variable length
• Using wrong number of underscores
• Confusing _ with %