LIKE & Wildcards

= only finds exact matches. To search for text that starts with, ends with or contains something, use LIKE with wildcard characters. It's the basis of simple search features.

The two wildcards

Starts with, ends with, contains

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

SELECT email FROM employees WHERE email LIKE '%a@shop.example';

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

Single-character matches

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

Names whose second letter is "a": one character, then a, then anything.

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

Orders in the first nine days of any month from January to September 2026. (For real date logic, prefer the date functions in lesson 16.)

Case sensitivity

Here databases genuinely differ:

• SQLite: LIKE ignores case for ASCII letters (but not for letters like é or ü).
• MySQL, SQL Server: usually case-insensitive, depending on the collation.
• PostgreSQL: LIKE is case-sensitive; ILIKE is the case-insensitive version.

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

A portable way to search without caring about case is to lower-case both sides: WHERE lower(name) LIKE '%lamp%'.

NOT LIKE

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

Every customer's email is at example.com — except Julia's, which is NULL. NULL NOT LIKE … is unknown, so she isn't returned either. Lesson 11 shows how to include such rows.

Searching for literal % or _

SELECT 'Save 20% today' LIKE '%20!%%' ESCAPE '!' AS has_percent;

ESCAPE names a character that makes the next wildcard literal: !% means an actual percent sign. The result is 1 (true) — SQLite and MySQL show booleans as 1 and 0.

Performance

A pattern with a fixed start ('Wire%') can use an index on the column, but one starting with a wildcard ('%less%') must check every row. For serious text search across large tables, use the database's full-text search features (SQLite FTS5, PostgreSQL tsvector, MySQL FULLTEXT).

Beyond LIKE

For more complex patterns, many databases support regular expressions: PostgreSQL's ~, MySQL's REGEXP, Oracle's REGEXP_LIKE. SQLite also has GLOB, which is case-sensitive and uses * and ? as wildcards.

SELECT name FROM products WHERE name GLOB '*[0-9]*';

Try it yourself

Find customers whose name contains "an" anywhere, in any case.