LIKE and wildcards

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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%';
Options
• Florachu and Firochu
• Firochu only
• Every name containing an o
• No row: % is not a letter

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.

Items to match
• 'A%'
• '%u'
• '%an%'
• '_ire'
Definitions
• Starts with A
• Ends with u
• Contains "an"
• Exactly 4 characters, ending with "ire"

Exercise 7 — Five letters exactly

Which pattern matches names that are exactly five characters long?

Options
• '_____'
• '%%%%%'
• '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".

Starter code
-- Write your query below
Back to the course