LIKE pattern matching in MySQL - Step-by-Step Execution

Concept Flow - LIKE pattern matching

Start with string to match

↓

Apply LIKE pattern

↓

Check each character

↓

Continue

↓

End of string?

↓

Match

The LIKE pattern matching checks a string against a pattern with wildcards, character by character, to find if it matches or not.

Execution Sample

MySQL

SELECT * FROM employees WHERE name LIKE 'J%n';

This query finds all employees whose names start with 'J' and end with 'n', with any characters in between.

Execution Table

Step	String Checked	Pattern Part	Match Result	Action
1	'John'	'J%n'	J matches J	Continue
2	'ohn'	'%n'	% matches 'oh'	Continue
3	'n'	'n'	n matches n	Continue
4	End of string	End of pattern	Full match	Return row
5	'Jason'	'J%n'	J matches J	Continue
6	'ason'	'%n'	% matches 'aso'	Continue
7	'n'	'n'	n matches n	Continue
8	End of string	End of pattern	Full match	Return row
9	'Jim'	'J%n'	J matches J	Continue
10	'im'	'%n'	% matches 'im'	Continue
11	End of string	'n'	No match	Skip row

💡 Stop when string or pattern ends or no match found

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7	After Step 8	After Step 9	After Step 10	After Step 11	Final
Current String Position	0	1	3	4	End	0	1	3	4	0	1	End	End
Current Pattern Position	0	1	2	3	End	0	1	2	3	0	1	2	End
Match Status	Pending	Match	Match	Match	Full match	Pending	Match	Match	Full match	Pending	Match	No match	No match

Key Moments - 2 Insights

Why does the '%' wildcard match multiple characters?

Because '%' can match zero or more characters, it allows the pattern to skip over any number of characters in the string, as shown in steps 2 and 6 where '%n' matches 'oh' and 'aso'.

Why does the pattern fail to match 'Jim'?

At step 11, the pattern expects 'n' at the end, but the string ends with 'm', so the match fails as shown in the execution_table row 11.

Visual Quiz - 3 Questions

Look at the execution table, what is the match result at step 6 for string 'Jason'?

A '%n' matches 'as'

B '%n' matches 'son'

C '%n' matches 'aso'

D '%n' matches 'on'

💡 Hint

Check the 'Pattern Part' and 'Match Result' columns at step 6 in the execution_table.

At which step does the pattern fail to match the string 'Jim'?

A Step 9

B Step 11

C Step 10

D Step 12

💡 Hint

Look at the 'Match Result' and 'Action' columns for 'Jim' in the execution_table.

If the pattern was 'J%n' and the string was 'Jan', what would happen at step 2?

A '%n' matches 'an'

B '%n' matches '' (empty string)

C '%n' matches 'a'

D No match

💡 Hint

Remember '%' matches zero or more characters; check how it matched 'ohn' and 'ason' in previous examples.

Concept Snapshot

LIKE pattern matching in SQL:
- '%' matches zero or more characters
- '_' matches exactly one character
- Pattern is matched left to right
- Returns rows where string fits pattern
- Useful for flexible text search

Full Transcript

The LIKE pattern matching in MySQL checks if a string fits a pattern that can include wildcards. The '%' wildcard matches any number of characters, including none, while '_' matches exactly one character. The matching process compares the string and pattern character by character, moving forward when characters match or when '%' allows skipping characters. If the entire pattern matches the string, the row is returned. If a mismatch occurs, the row is skipped. For example, the pattern 'J%n' matches names starting with 'J' and ending with 'n', with anything in between. This is useful for searching text flexibly in databases.

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 %