LambdaLabTM
Databases & SQL · Class 12 · Querying with SELECT
MySQLSELECT⏱️ 8 min read

Pattern Matching with LIKE

= needs the whole value, exactly. But you often know only a part of it — names starting with A, anything containing “kumar”. like compares text against a pattern, and the pattern is built from just two special characters.

1The two wildcards

%
Any number of characters

Including none at all. Stands for a run of any length.

_
Exactly one character

One, and it must be there. A single underscore.

Not the same symbols as elsewhere
If you know file searching you may expect * and ?. SQL uses % and _. And % here has nothing to do with percentages or with the % remainder operator in Python.

2Starting with something

MySQL command line client
mysql> select id, name from students where name like 'A%';
+------+--------+
| id | name |
+------+--------+
| 1187 | Anjali |
+------+--------+
1 row in set (0.00 sec)

'A%' reads as: an A, then anything. Only Anjali qualifies.

3Ending with something

MySQL command line client
mysql> select id, name from students where name like '%a';
+------+-------+
| id | name |
+------+-------+
| 1099 | Veena |
| 2044 | Karma |
| 2199 | Diana |
| 3120 | Pema |
| 3612 | Nima |
+------+-------+
5 rows in set (0.00 sec)

Anything, then an a. Put the % at the front for “ends with” and at the back for “starts with” — the wildcard marks the part you do not care about.

4Containing something

A wildcard at both ends means the letter can be anywhere:

MySQL command line client
mysql> select id, name from students where name like '%a%';
+------+--------+
| id | name |
+------+--------+
| 1099 | Veena |
| 1187 | Anjali |
| 1250 | Sohan |
| 1402 | Lhamu |
| 2035 | Ravi |
| 2044 | Karma |
| 2199 | Diana |
| 3120 | Pema |
| 3301 | Farhan |
| 3612 | Nima |
+------+--------+
10 rows in set (0.00 sec)

Ten of the twelve names contain an a somewhere. Only Bhim and Tom do not.

5Counting characters with _

Four underscores and nothing else means a name of exactly four characters:

MySQL command line client
mysql> select id, name from students where name like '____';
+------+------+
| id | name |
+------+------+
| 2035 | Ravi |
| 2500 | Bhim |
| 3120 | Pema |
| 3612 | Nima |
+------+------+
4 rows in set (0.00 sec)

Ravi, Bhim, Pema and Nima. Tom, with three letters, does not match — each _ must be filled by exactly one character, so the count has to be right.

The two wildcards can be combined:

MySQL command line client
mysql> select id, name from students where name like 'S%n';
+------+-------+
| id | name |
+------+-------+
| 1250 | Sohan |
+------+-------+
1 row in set (0.00 sec)

Starts with S, ends with n, anything in between. Sohan matches; Singtam would too, if it were a name in this column.

6LIKE works on dates too

A date is stored as 'YYYY-MM-DD', and like is happy to treat that as text — which gives you a neat way to match a year, a month or a day without any date functions at all.

Everyone born in 2008 — the year is at the front:

MySQL command line client
mysql> select id, name, dob from students where dob like '2008%';
+------+--------+------------+
| id | name | dob |
+------+--------+------------+
| 1187 | Anjali | 2008-03-04 |
| 1250 | Sohan | 2008-12-01 |
| 2035 | Ravi | 2008-08-13 |
| 2500 | Bhim | 2008-06-22 |
+------+--------+------------+
4 rows in set (0.00 sec)

Everyone born in March, whatever the year — the month sits between two hyphens:

MySQL command line client
mysql> select id, name, dob from students where dob like '%-03-%';
+------+--------+------------+
| id | name | dob |
+------+--------+------------+
| 1187 | Anjali | 2008-03-04 |
| 3120 | Pema | 2010-03-28 |
| 3301 | Farhan | 2012-03-09 |
+------+--------+------------+
3 rows in set (0.00 sec)

And everyone born on the 20th of a month — the day is at the end:

MySQL command line client
mysql> select id, name, dob from students where dob like '%-20';
+------+-------+------------+
| id | name | dob |
+------+-------+------------+
| 1099 | Veena | 2010-11-20 |
+------+-------+------------+
1 row in set (0.00 sec)

The underscore is useful here too, because the parts of a date have a fixed width. Two digits, a hyphen, two digits — so '2010-__-__' is another way to say “born in 2010”:

MySQL command line client
mysql> select id, name, dob from students where dob like '2010-__-__';
+------+-------+------------+
| id | name | dob |
+------+-------+------------+
| 1099 | Veena | 2010-11-20 |
| 1402 | Lhamu | 2010-09-30 |
| 3120 | Pema | 2010-03-28 |
+------+-------+------------+
3 rows in set (0.00 sec)
Where the hyphens matter
Write '%03%' rather than '%-03-%' and you would also match a day of 03, or the 03 inside a year like 2003. Including the hyphens pins the digits to the month position, which is what makes the pattern mean what you intended.
There is another way to do this
Informatics Practices students have date functions for exactly this job — where year(dob) = 2008 or where monthname(dob) = "March", in the Date Functions lesson. They read better. like is the tool that works in both syllabuses, and it is worth knowing that a date can be pattern-matched at all.

7Patterns at a glance

Reading a pattern
PatternMeansMatches
'A%'Starts with AAnjali, Asha
'%a'Ends with aVeena, Karma, Diana
'%a%'Contains an a anywhereSohan, Farhan, Ravi
'____'Exactly four charactersRavi, Bhim, Pema
'_o%'Second character is oSohan, Tom
'S%n'Starts with S, ends with nSohan
'%'Anything at allEvery row with a value
not like
Every pattern can be reversed with not like, in the same way as not in and not between. So where name not like 'A%' gives every student whose name does not begin with A.

8Recap

%

Any number of characters, including none.

_

Exactly one character, which must be present.

like 'A%'

Starts with. '%a' ends with. '%a%' contains.

The pattern is quoted

It is a piece of text, like any other value.

Quick Check

Which pattern finds names ending in 'a'?

Quick Check

What does like '____' (four underscores) match?

Quick Check

Which finds every student whose name contains the letters 'ar' anywhere?

← Previous
🕳️ IS NULL & IS NOT NULL
Next →
DISTINCT 🎭