SQL and MySQL course Β· Module 3: Filtering (WHERE)
Searching patterns: LIKE
In this lesson3
Sometimes we don't know the exact value - we only remember that a title starts with the letter O, or that a surname contains the syllable "kow". For such fuzzy searching we use the LIKE operator together with wildcard characters.
The percent sign %
The most important wildcard is the percent %, which replaces any sequence of characters (including empty):
SELECT * FROM ksiazki
WHERE tytul LIKE 'O%';This means: "Titles starting with O" - after the letter O there can be anything (e.g. Odyssey, Old scroll).
Three popular patterns
'O%'- starts with O (percent at the end),'%a'- ends with the letter a (percent at the beginning),'%war%'- contains the word "war" anywhere in the middle (percent on both sides).
SELECT * FROM ksiazki
WHERE tytul LIKE '%war%';This query will find any title in which the word "war" appears - at the beginning, in the middle or at the end.
Remember the quotes
A pattern in LIKE is text, so we always wrap it in single quotes:
SELECT * FROM czytelnicy
WHERE nazwisko LIKE 'Kow%';This will find all readers whose surname starts with "Kow" - for example Kowalski or Kowalczyk. LIKE is the scribe's eye able to recognize a scroll even from a fragment of its title!
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What will the condition match: WHERE tytul LIKE 'A%' ?
2. What will the condition match: WHERE tytul LIKE '%war%' ?
Hands-on tasks in the game
- Code editor
Write a query that retrieves all columns from the ksiazki table for books whose tytul starts with the letter O. Use the LIKE operator.
- Click in order
Click the elements in the order in which we build a query searching for titles that start with the letter O:
- Code editor
Write a query that retrieves all columns from the czytelnicy table for readers whose nazwisko starts with 'Kow'. Use the LIKE operator.