Spec & Goals 3 min
AQA Spec 3.7.2 — Pattern matching with LIKE and wildcards
By the end of this lesson you can:
- Use
LIKEto match a pattern instead of an exact value. - Use the wildcard
%(any characters) and_(one character). - Predict which records a
LIKEpattern returns.
Warm-Up 5 min
WHERE Town = 'Penang' needs an exact match. But what if you want every town starting with "P", or every name containing "ai"? That needs pattern matching.
Quick starter
How would you ask for all students whose name begins with the letter A?
Reveal the idea
Match the pattern "A followed by anything": WHERE Name LIKE 'A%'. The % stands for any characters.
Key Concept — matching patterns 14 min
LIKE is used in a WHERE clause to match a pattern in text, using wildcards.
| Wildcard | Matches | Pattern | Example matches |
|---|---|---|---|
% | any number of characters (incl. none) | 'P%' | Penang, Perak, P |
% | ends with… | '%a' | Aisyah ✗, Melaka ✓ (ends "a") |
% | contains… | '%ai%' | Aisyah, Cairo |
_ | exactly one character | '10_' | 10A, 10B (not 10AB) |
Our Student table (reminder)
| Name | Form | Town |
|---|---|---|
| Aisyah | 10A | Ipoh |
| Wei Jie | 10B | Penang |
| Priya | 10A | Ipoh |
| Arjun | 11C | Johor Bahru |
| Mei Ling | 11C | Penang |
| Hafiz | 10B | Kuala Lumpur |
Worked Example — patterns in action 12 min
Query 1: students whose Town starts with "P".
SELECT Name, Town FROM Student WHERE Town LIKE 'P%';
| Name | Town |
|---|---|
| Wei Jie | Penang |
| Mei Ling | Penang |
Query 2: students whose Name contains "i".
SELECT Name FROM Student WHERE Name LIKE '%i%';
| Name |
|---|
| Aisyah |
| Wei Jie |
| Priya |
| Mei Ling |
| Hafiz |
Query 3: forms in year 10 (pattern 10 + one character).
SELECT DISTINCT Form FROM Student WHERE Form LIKE '10_';
Matches 10A and 10B (not 11C).
Try It Yourself 12 min
Goal: Write a query for students whose Name starts with "A".
Goal: Write a query for towns ending in "h", showing Name and Town.
Hint: '%h'.
Goal: Explain the difference between LIKE 'A%', LIKE '%A' and LIKE '_A%'.
📝 Exam Practice 10 min
State what the wildcards % and _ mean in a LIKE pattern.
Mark scheme
%— any number of characters (including none) (1)._— exactly one character (1).
Write a query to display the Name of every student whose name contains the letters "ei".
Mark scheme
SELECT Name FROM Student(1).WHERE Name LIKE(1).'%ei%';— correct wildcards both sides (1).
State the result of SELECT Name FROM Student WHERE Town LIKE 'I%';
Mark scheme
- Aisyah and Priya (both from Ipoh) (1).
Recap & Key Terms 3 min
LIKE matches a text pattern using wildcards: % stands for any characters, _ for exactly one. Placement sets the meaning — 'A%' starts with A, '%A' ends with A, '%A%' contains A.
- LIKE
- An SQL operator that matches a pattern of characters using wildcards.
- % (wildcard)
- Matches any number of characters, including none.
- _ (wildcard)
- Matches exactly one character.
Homework 1 min
Task (≤ 15 min): For a Book(Title, Author) table, write queries for: (a) titles starting with "The"; (b) authors containing "lee"; (c) titles exactly four characters long.
Model answer
-- (a) SELECT Title FROM Book WHERE Title LIKE 'The%'; -- (b) SELECT Author FROM Book WHERE Author LIKE '%lee%'; -- (c) SELECT Title FROM Book WHERE Title LIKE '____';
Award marks for: correct starts-with pattern (1); correct contains pattern (1); four underscores for four characters (1).