LIKE pattern matching in MySQL - Mini Project: Build & Apply

Using LIKE Pattern Matching in MySQL

📖 Scenario: You are managing a small bookstore database. You want to find books based on parts of their titles or authors' names.

🎯 Goal: Build a simple MySQL query using the LIKE operator to find books with titles or authors matching specific patterns.

📋 What You'll Learn

Create a table called books with columns id, title, and author.

Insert exactly three books with given titles and authors.

Create a variable to hold a pattern string for searching.

Write a SELECT query using LIKE to find books matching the pattern in the title.

Add a condition to also match the author name using LIKE.

💡 Why This Matters

🌍 Real World

Searching for books or products by partial names is common in online stores and libraries.

💼 Career

Knowing how to use LIKE helps in writing flexible search queries in databases for many applications.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns id (integer primary key), title (varchar 100), and author (varchar 100). Then insert these three rows exactly: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, 'Great Expectations', 'Charles Dickens'), and (3, 'Gone with the Wind', 'Margaret Mitchell').

MySQL

-- Create the books table and insert three rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Define a pattern variable for searching

Create a variable called @pattern and set it to the string '%Great%' to search for titles or authors containing the word 'Great'.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, 'Great Expectations', 'Charles Dickens'),
(3, 'Gone with the Wind', 'Margaret Mitchell');

-- Set the pattern variable
-- Your code here

Hint

Use SET @pattern = '%Great%'; to create the variable.

3

Write a SELECT query using LIKE on the title

Write a SELECT statement to get all columns from books where the title matches the pattern stored in @pattern using the LIKE operator.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, 'Great Expectations', 'Charles Dickens'),
(3, 'Gone with the Wind', 'Margaret Mitchell');

SET @pattern = '%Great%';

-- Select books where title matches the pattern
-- Your code here

Hint

Use SELECT * FROM books WHERE title LIKE @pattern; to find matching titles.

4

Extend the query to match author names too

Modify the SELECT query to also return rows where the author matches the pattern stored in @pattern. Use OR to combine the conditions on title and author with LIKE.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, 'Great Expectations', 'Charles Dickens'),
(3, 'Gone with the Wind', 'Margaret Mitchell');

SET @pattern = '%Great%';

-- Modify the query to also match author names
-- Your code here

Hint

Use OR author LIKE @pattern to include authors in the search.

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 %