MySQL LIKE Operator: Search Text with % and

LIKE matches text against a pattern. Two characters are special:

% matches any number of characters (including none).
_ matches exactly one character.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

Starts with, ends with, contains

query.sql

SQL

1
2
3

SELECT name FROM products WHERE name LIKE 'S%' ORDER BY name;
SELECT name FROM products WHERE name LIKE '%Set' ORDER BY name;
SELECT name FROM products WHERE name LIKE '%oo%' ORDER BY name;

Output

OUTPUT

+--------------+
| name         |
+--------------+
| Sticky Notes |
+--------------+
1 row in set

+------------+
| name       |
+------------+
| Marker Set |
+------------+
1 row in set

+----------+
| name     |
+----------+
| Notebook |
+----------+
1 row in set

Pattern	Meaning
`'S%'`	Starts with S
`'%Set'`	Ends with Set
`'%oo%'`	Contains `oo` anywhere
`'_ack%'`	One character, then `ack` (Backpack)
`'Gel Pen'`	Exactly this (same as `=`)

One character with _

query.sql

SQL

1
2

SELECT name FROM products WHERE name LIKE '_ack%';
SELECT name FROM products WHERE name LIKE '______' ORDER BY name;

Output

OUTPUT

+----------+
| name     |
+----------+
| Backpack |
+----------+
1 row in set

Empty set

The second pattern has six underscores: names that are exactly six characters long.

NOT LIKE

query.sql

SQL

SELECT name FROM products WHERE name NOT LIKE '%e%' ORDER BY name;

Output

OUTPUT

+----------+
| name     |
+----------+
| Backpack |
+----------+
1 row in set

Case and accents

LIKE follows the column’s collation. With the MySQL 8 default (utf8mb4_0900_ai_ci), LIKE ignores case and accents:

query.sql

SQL

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

Output

OUTPUT

+---------+
| name    |
+---------+
| Gel Pen |
+---------+
1 row in set

For a case-sensitive match, add a case-sensitive collation, or cast the pattern to BINARY:

query.sql

SQL

1
2

SELECT name FROM products WHERE name LIKE CAST('%PEN%' AS BINARY);
SELECT name FROM products WHERE name LIKE '%Pen%' COLLATE utf8mb4_0900_as_cs;

Output

OUTPUT

Empty set

+---------+
| name    |
+---------+
| Gel Pen |
+---------+
1 row in set

Searching for a real % or _

To find text that contains the characters % or _, escape them with a backslash (or choose your own escape character with ESCAPE):

query.sql

SQL

CREATE TABLE coupons (code VARCHAR(20));
INSERT INTO coupons VALUES ('SAVE_10'), ('SAVE-10'), ('SAVE%20'), ('SAVE20');

SELECT code FROM coupons WHERE code LIKE 'SAVE_10' ORDER BY code;
SELECT code FROM coupons WHERE code LIKE 'SAVE\_10' ORDER BY code;
SELECT code FROM coupons WHERE code LIKE 'SAVE!%20' ESCAPE '!' ORDER BY code;

Output

OUTPUT

+---------+
| code    |
+---------+
| SAVE_10 |
| SAVE-10 |
+---------+
2 rows in set

+---------+
| code    |
+---------+
| SAVE_10 |
+---------+
1 row in set

+---------+
| code    |
+---------+
| SAVE%20 |
+---------+
1 row in set

Without the backslash, _ matches any character, so SAVE_10 also matches SAVE-10.

LIKE and indexes

An index on a text column can be used when the pattern starts with fixed characters:

Pattern	Index used?
`'Note%'`	Yes. MySQL turns it into a range `Note` to `Notf`.
`'%book'`	No. It must read every row.
`'%oo%'`	No
`'_otebook'`	No

query.sql

SQL

CREATE TABLE people (id INT AUTO_INCREMENT PRIMARY KEY, last_name VARCHAR(40) NOT NULL, KEY idx_last (last_name));
INSERT INTO people (last_name) VALUES ('Rao'), ('Rathod'), ('Nair'), ('Singh'), ('Shah');

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM people WHERE last_name LIKE 'Ra%';
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM people WHERE last_name LIKE '%a%';

Output

OUTPUT

+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | people | NULL       | range | idx_last      | idx_last | 162     | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
1 row in set

+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | people | NULL       | index | NULL          | idx_last | 162     | NULL |    5 |    20.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
1 row in set

The first plan has type = range and uses idx_last. The second has type = index, which means it scans the whole index. On a table with millions of rows, the difference is huge.

When you need a real text search

LIKE '%word%' on a big table is slow and finds only exact substrings. For searching words in articles, product descriptions or comments, use full-text search (Full-Text Search). For complex patterns, use REGEXP (REGEXP).

dangerous

User input in LIKE

If a search box value goes into a LIKE pattern, a user can type % or _ and match everything. Escape those characters in your application, and use prepared statements for the value (SQL Injection).