AI Hints
Homeโ€บSQL Tutorialโ€บRanges, Patterns and Missing Values
SQL ยท Lesson 14 of 126

Patterns with LIKE

Search inside text.

โฑ 2 min readโ–ถ Video at 19:59๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

A customer phones Helen. I can't remember the title, but I know it started with The. Equals won't help here, because we don't know the whole title. We need a pattern. That's what like is for.

Patterns with LIKE
From the video: Patterns with LIKE
  • I only remember part of the title
  • Equals needs the whole thing
  • Search with a pattern

Starts with

Like uses a special symbol, the percent sign. Percent means, any text at all, or nothing. So, The followed by percent means, starts with The. Run it. Eight books start with The.

query.sqlsql
SELECT title
FROM books
WHERE title LIKE 'The%';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          title           โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ The Silent Orchard       โ”‚
โ”‚ The Last Lighthouse      โ”‚
โ”‚ The Quiet Kitchen        โ”‚
โ”‚ The Map Maker's Daughter โ”‚
โ”‚ The Calm Mind            โ”‚
โ”‚ The Glass Forest         โ”‚
โ”‚ The Pricing Game         โ”‚
โ”‚ The Painted Door         โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Contains

Put percent at both ends to search anywhere inside the text. Here we look for the word of, with a space on each side, so we don't match words like often. Run it. Six titles contain the word of.

query.sqlsql
SELECT title
FROM books
WHERE title LIKE '% of %';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          title           โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Code of the River        โ”‚
โ”‚ A Short History of Bread โ”‚
โ”‚ Dragons of Ember Bay     โ”‚
โ”‚ Garden of Small Things   โ”‚
โ”‚ Crown of Ash             โ”‚
โ”‚ Empires of Salt          โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

LIKE cheat sheet

Here's a cheat sheet. Starts with, put percent at the end. Ends with, put percent at the start. Contains, put percent at both ends. And an_matches exactly one character. One more thing. In SQLite, like ignores capital letters, but in some other databases it doesn't, so take care.

'The%'starts with The
'%Bay'ends with Bay
'%of%'contains of
'_ook'one character, then ook

Summary

Like searches text with patterns, using percent for any text. Next, the strangest value in SQL. Nothing at all.

Key takeaways
  • % means any text
  • '%word%' means contains
  • _ means one character
Next up: Missing Values: NULL โ€” the value that is not there