Learn SQL: LIKE
Searching text with LIKE
A customer can't remember the title. "Something with 'the' in it, I think?" Vic sighs. With `=` you won't get far here; with `LIKE` you will.
Patterns with % and _
LIKE compares text with a pattern. % stands for zero, one or more characters of any kind. _ stands for exactly one character.
So 'The %' means: starts with "The " followed by anything.
SELECT title FROM films WHERE title LIKE 'The %'| title |
|---|
| The Matrix |
| The Shining |
| The Lion King |
Anywhere in the text
Put % on both sides and you search anywhere in the text: '%in%' finds every title with "in" in it.
In SQLite, LIKE makes no distinction between capitals and lower-case letters. In PostgreSQL it does: there you use ILIKE to ignore case.
SELECT title FROM films WHERE title LIKE '%in%'| title |
|---|
| The Shining |
| Finding Nemo |
| The Lion King |
| Inception |
Try it yourself
Which films have a title that starts with an S? Show title and year.
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson