LIKE & Wildcards

When a visitor types "phone" into a shop's search box, they expect to find "Noise-Cancelling Headphones", even though nobody typed the full name. The = operator cannot do that: it only matches the whole value exactly. For partial matches, SQL has the LIKE operator.

In this lesson you will use the two wildcard characters, % and _, to find text that starts with, ends with or contains something. You will also learn about case, escaping and why some LIKE searches are slow.

LIKE and the % wildcard

LIKE compares a value with a pattern. Inside the pattern, the percent sign % stands for "any number of characters, including none". Here is the search box example:

SELECT product_name, price
FROM products
WHERE product_name LIKE '%phone%';

product_name                | price
----------------------------+------
Noise-Cancelling Headphones | 8999
(1 row)

The pattern '%phone%' means "anything, then phone, then anything". Where you put the % decides what kind of match you get:

Customers whose first name starts with A:

SELECT first_name, last_name
FROM customers
WHERE first_name LIKE 'A%';

first_name | last_name
-----------+----------
Aarav      | Sharma
Ananya     | Iyer
Arjun      | Reddy
(3 rows)

Customers whose surname ends with the letter a:

SELECT first_name, last_name
FROM customers
WHERE last_name LIKE '%a';

first_name | last_name
-----------+----------
Aarav      | Sharma
Priya      | Verma
Rohan      | Mehta
Sneha      | Gupta
(4 rows)

The _ wildcard: exactly one character

The underscore _ stands for exactly one character, no more and no less. You can use several in a row, and mix them with %. This query finds first names whose second letter is a:

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

first_name
----------
Aarav
Kabir
Farhan
(3 rows)

The _ matches the first letter, whatever it is, then the a must come next, then % allows anything after it. In the same way, LIKE '____' (four underscores) finds names that are exactly four characters long, such as Isha.

NOT LIKE

Put NOT in front of LIKE to keep the rows that do not match the pattern. This lists every employee whose job title does not contain the word Manager:

SELECT first_name, job_title
FROM employees
WHERE job_title NOT LIKE '%Manager%';

first_name | job_title
-----------+------------------
Rajesh     | Chief Executive
Amit       | Senior Developer
Pooja      | Developer
Karan      | Developer
Rahul      | Sales Executive
Nisha      | Sales Executive
Deepak     | Support Lead
Tanvi      | Support Executive
(8 rows)

Neha and Simran are gone, because their titles contain "Manager". Remember that a NULL value never matches LIKE or NOT LIKE, because the result is unknown (see NULL Values).

Capital letters

With MySQL's default settings, LIKE ignores capital letters, so 'a%' finds Aarav and Ananya just as 'A%' does. MySQL even ignores accents by default, so 'e%' would also match a name starting with é. The browser editor's SQLite also ignores the case of English letters in LIKE, so the examples here behave the same in both.

If you ever need a case-sensitive match in MySQL, compare with a binary collation:

SELECT first_name FROM customers
WHERE first_name LIKE 'a%' COLLATE utf8mb4_bin;

This returns no rows, because every first name starts with a capital letter.

Searching for a real % or _

What if the text you are searching for contains a % or _ itself, such as a coupon called FLAT_50%? Choose an escape character, put it before the wildcard you want to treat as normal text, and name it with ESCAPE:

SELECT * FROM coupons
WHERE code LIKE '%!_50!%' ESCAPE '!';

Here !_ means a real underscore and !% a real percent sign. (The practice database has no coupons table; this is only to show the syntax.)

LIKE and speed

A pattern that starts with fixed text, such as 'Gra%', can use an index on the column, so MySQL finds the matches quickly even in a huge table. A pattern that starts with %, such as '%phone%', cannot: MySQL has to check every single row. That is fine for a table of a few thousand rows, but for searching large amounts of text MySQL offers full-text indexes, which are built for this job. You will learn about indexes in Indexes & EXPLAIN.

Common mistakes

• Using = with wildcards. WHERE first_name = 'A%' looks for a name that is literally the two characters A and %. Wildcards only work with LIKE.
• Forgetting the %. WHERE product_name LIKE 'Monitor' finds nothing, because no name is exactly "Monitor". Write '%Monitor%'.
• Using * and ? as wildcards. Those work in file searches and some other programs, not in SQL. Use % and _.
• Using LIKE on dates. WHERE order_date LIKE '2025-03%' may seem to work, but it turns each date into text first and cannot use an index. Use a range instead: order_date >= '2025-03-01' AND order_date < '2025-04-01'.

Key points

• LIKE compares text with a pattern; = only matches exact values.
• % matches any number of characters (including none); _ matches exactly one.
• NOT LIKE keeps rows that do not match; NULL never matches either.
• MySQL's LIKE ignores case and accents by default; add a binary collation for an exact-case match.
• Use ESCAPE to search for a literal % or _.
• Patterns that start with % cannot use an index and are slow on big tables.

SELECT product_name FROM products
WHERE product_name LIKE '%GB%' OR product_name LIKE '%TB%';

SELECT first_name, job_title FROM employees
WHERE job_title LIKE '%Executive';