LIKE and wildcards
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Patterns with LIKE
= only finds exact text. To search for text that *starts with*, *ends with* or *contains* something, SQL uses LIKE with a pattern.
The % wildcard means "any sequence of characters, even empty". Let's find the names that start with P:
SELECT name
FROM codemon
WHERE name LIKE 'P%';Pyrochu and Plantachu match: the pattern says "a P, then anything".
Case sensitivity
In SQLite, LIKE ignores the case of ASCII letters: 'p%' also matches Pyrochu. Other databases differ (PostgreSQL's LIKE is case-sensitive and offers ILIKE). Always check your database's behaviour.
Exercise 2 — Anywhere, or at the end
Move the % to change the meaning of the pattern. With a % on each side, the text can appear anywhere:
SELECT name
FROM codemon
WHERE name LIKE '%ro%';With the % at the beginning only, the pattern means "ends with":
SELECT name
FROM codemon
WHERE name LIKE '%chu';NOT LIKE excludes the matches. Here are the types that do not contain the letter a:
SELECT name, type
FROM codemon
WHERE type NOT LIKE '%a%';Cheat sheet
'abc%' starts with abc · '%abc' ends with abc · '%abc%' contains abc · 'abc' (no wildcard) behaves like =.
Exercise 3 — Starts with F
Which Codémons does this query return?
SELECT name
FROM codemon
WHERE name LIKE 'F%';Hint : 'F%' means: an F first, then any sequence of characters.
Exercise 4 — Somewhere in the name
Complete the query to find the Codémons whose name contains "ro" anywhere.
Hint : Use LIKE with a % on both sides of the text you're looking for.
Exercise 5 — Exactly one character: _
The second wildcard is the underscore _. It stands for exactly one character, no more, no less.
SELECT name
FROM codemon
WHERE name LIKE 'P_rochu';Only Pyrochu fits: P, one character, then "rochu". Plantachu is too long for this pattern.
Chain several underscores to match a precise length. Seven underscores = names of exactly seven characters:
SELECT name
FROM codemon
WHERE name LIKE '_______';% and _ together
The two wildcards can be mixed: '_a%' means "any first character, then an a, then anything" — it would match "Water" or "Magma".
Exercise 6 — Decode the patterns
Match each pattern with what it matches.
'A%''%u''%an%''_ire'Exercise 7 — Five letters exactly
Which pattern matches names that are exactly five characters long?
'_____''%%%%%''5''_%'Hint : Each underscore stands for exactly one character; five of them means five characters.
Exercise 8 — Not that letter
Complete the query to keep only the Codémons whose type does not contain the letter a.
Hint : Put the negation keyword in front of LIKE, and let the pattern allow anything before and after the a.
Exercise 9 — Name detective
A trainer only remembers that the Codémon she saw had "ro" in its name and that its type started with an f.
Write a query that returns the name and type of the Codémons whose name contains "ro" and whose type starts with "f".
-- Write your query below