SQL and MySQL course Β· Module 3: Filtering (WHERE)

Searching patterns: LIKE

2 min read Β·
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).

  • '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. 1. What will the condition match: WHERE tytul LIKE 'A%' ?

  2. 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.

  1. QuestionThe percent wildcard
  2. QuestionPercent on both sides
  3. Coding taskTitles starting with O
  4. Click in orderA LIKE pattern for titles
  5. Coding taskSurnames of the Kowalskis

Useful articles