LIKE pattern matching in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL LIKE operator do?

The LIKE operator is used to search for a specified pattern in a column. It helps find rows where the column's value matches the pattern.

beginner

What does the % symbol mean in a LIKE pattern?

The % symbol matches zero or more characters of any kind. For example, 'a%' matches any string starting with 'a'.

beginner

What does the _ (underscore) symbol mean in a LIKE pattern?

The _ symbol matches exactly one character. For example, '_at' matches 'cat', 'bat', but not 'at' or 'chat'.

beginner

How would you write a query to find all names that end with 'son'?

Use: SELECT * FROM table WHERE name LIKE '%son'; This finds all names ending with 'son'.

intermediate

Can LIKE be case sensitive in MySQL?

By default, LIKE is case-insensitive in MySQL for non-binary strings. But it can be case-sensitive if the column uses a binary collation.

Which wildcard matches any number of characters in a LIKE pattern?

A _

B %

C *

D #

The % wildcard matches zero or more characters in a LIKE pattern.

What does the pattern '_at' match in a LIKE query?

A Any three-letter string with 'at' as last two letters

B Any string starting with 'at'

C Any string containing 'at' anywhere

D Any string ending with 'at'

The _ matches exactly one character, so '_at' matches three-letter strings ending with 'at'.

How do you find rows where a column starts with 'abc' using LIKE?

A LIKE '_abc'

B LIKE '%abc'

C LIKE 'abc%'

D LIKE 'abc'

The pattern 'abc%' matches any string starting with 'abc'.

Is the LIKE operator case sensitive by default in MySQL?

A Yes, always case sensitive

B No, always case insensitive

C Only for numeric columns

D Depends on the column collation

LIKE is case insensitive by default for non-binary strings but depends on the column's collation.

Which query finds all rows where 'city' contains 'York' anywhere?

A WHERE city LIKE '%York%'

B WHERE city LIKE 'York%'

C WHERE city LIKE '_York'

D WHERE city LIKE 'York'

The pattern '%York%' matches any string containing 'York' anywhere.

Explain how the LIKE operator works and how to use % and _ wildcards in pattern matching.

Think about searching for words that start, end, or contain certain letters.

Describe how case sensitivity affects LIKE queries in MySQL and how it can be controlled.

Consider how uppercase and lowercase letters are treated in searches.

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 %