Lesson 5 of 26 · see the practice database

Searching text with LIKE

The equals sign finds exact matches. Often you know only part of what you are looking for: names that start with A, products with "kg" in them, email addresses at a certain site. LIKE matches text against a pattern.

The two wildcards

A pattern is ordinary text plus two special characters.

Starts with

Put the % at the end.

SELECT name FROM customers WHERE name LIKE 'A%';

The pattern 'A%' means "an A, then anything".

Ends with

Put the % at the start.

SELECT name, email FROM customers WHERE email LIKE '%@example.com';

SELECT name FROM products WHERE name LIKE '%kg';

Contains

Put a % on both sides.

SELECT name FROM products WHERE name LIKE '%Box%';

This finds the text anywhere: at the start, in the middle or at the end, because % can also stand for nothing.

One character with the underscore

Each underscore must match exactly one character. This finds names whose second letter is a.

SELECT name FROM customers WHERE name LIKE '_a%';

Underscores are useful for codes with a fixed shape. The pattern '2026-0_-__' matches every date from January to September 2026.

SELECT id, order_date FROM orders WHERE order_date LIKE '2026-0_-1_';

That pattern picks the days from the 10th to the 19th of those months.

NOT LIKE

SELECT name FROM products WHERE name NOT LIKE '% %';

The pattern '% %' matches any text with a space in it, so NOT LIKE returns the names made of a single word.

Capital and small letters

How LIKE treats case depends on the database.

• In SQLite, LIKE ignores case for the English letters A to Z. That is how the practice box behaves.
• In MySQL and SQL Server, with their usual settings, it also ignores case.
• In PostgreSQL and Oracle, LIKE is case sensitive. PostgreSQL has a separate word, ILIKE, for a search that ignores case.

SELECT name FROM products WHERE name LIKE '%box%';

To be safe in any database, turn both sides to the same case, as the lesson on functions shows.

Combining patterns

LIKE conditions join with AND and OR like any others.

SELECT name
FROM customers
WHERE name LIKE 'A%' OR name LIKE 'S%';

There is no way to give LIKE a list of patterns in standard SQL. Repeat the test with OR.

Searching for a real percent sign

What if the text you want contains % or _ itself? Name an escape character with ESCAPE. Whatever follows that character in the pattern is taken as itself.

SELECT '50% off' LIKE '%\%%' ESCAPE '\' AS has_percent,
       'half off' LIKE '%\%%' ESCAPE '\' AS no_percent;

The result is 1 for true and 0 for false, which is how SQLite shows the answer to a test.

LIKE can be slow

A pattern that starts with % forces the database to read every row and look inside the text. On a table of a few thousand rows nobody notices. On millions of rows it is slow. A pattern with a fixed start, such as 'A%', can use an index and stays fast. Large systems that need "contains" searches use special full-text search features instead.

LIKE or equals?

If you know the whole value, use =. It is exact and fast. Use LIKE only when you need a wildcard. A LIKE without any wildcard works but tells the reader you meant something else.

Try it yourself

SELECT name FROM customers WHERE name LIKE '% Khan' OR name LIKE '% Kumar';

SELECT name, price FROM products WHERE name LIKE '%1 L%';

SELECT name FROM employees WHERE name LIKE '_a%';

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.