SQLearnAcademy
←Filtering data: WHERE
Lesson 6 / 8

Module 02 · lesson 6

LIKE: pattern search

A user types "Coffee" into the site search. Exact equality isn't enough here — you need to find every name that contains this word.

You'll need:WHERE: keeping only the rows you need(opens in a new tab)

Pattern search

LIKE searches text by a pattern with wildcard characters:

  • % — any sequence of characters (including an empty one);
  • _ — exactly one character of any kind.
SELECT name 
FROM products
WHERE name LIKE 'S%';   -- name starts with "S"

Example patterns:

  • '%Wireless%' — contains "Wireless";
  • 'Run%' — starts with "Run";
  • '%Card' — ends with "Card".

LIKE is case-sensitive. In PostgreSQL there's ILIKE — the same search but case-insensitive ('%sql%' will also find "SQL").

% — будь-яка кількість символів · _ — рівно один символ% — any number of characters · _ — exactly one character % — cualquier cantidad de caracteres · _ — exactamente uno ШаблонPattern PatrónЩо означаєWhat it means Qué significaПриклад збігуExample match Ejemplo 'S%' '%Coffee%' '%Card' починається з «S»starts with 'S' empieza por 'S' містить «Coffee»contains 'Coffee' contiene 'Coffee' закінчується на «Card»ends with 'Card' termina en 'Card' SoundMax…, SQL for Beginners Coffee Maker Gift Card

Your turn

The user is searching for "Coffee". Find all products whose name contains this word (output name).

Summary

  • % — any text, _ — a single character.
  • 'Run%' — starts with; '%Wireless%' — contains.
  • LIKE is case-sensitive; ILIKE isn't (Postgres).