SQL LIKE Operator and Wildcards

= only finds exact matches. LIKE finds patterns: names that start with “A”, emails at a certain domain, product names that contain “Desk”. Two wildcard characters do the work: % for any number of characters and _ for exactly one.

The two wildcards

Sample data

Starts with

Customers whose name starts with “R”:

SELECT customer_nameFROM customersWHERE customer_name LIKE 'R%';

Ends with

Customers with an example.com email address:

SELECT customer_name, emailFROM customersWHERE email LIKE '%@example.com';

Contains

Products whose name contains “Desk” anywhere:

SELECT product_nameFROM productsWHERE product_name LIKE '%Desk%';

The underscore: exactly one character

Employees whose first name has exactly four letters:

SELECT first_nameFROM employeesWHERE first_name LIKE '____';

Second letter is “a”:

SELECT first_nameFROM employeesWHERE first_name LIKE '_a%';

NOT LIKE

SELECT product_nameFROM productsWHERE product_name NOT LIKE '%e%';

Escaping % and _

To search for a real percent sign or underscore, put a backslash in front of it:

CREATE TABLE coupons (code VARCHAR(20));INSERT INTO coupons VALUES ('SAVE10%'), ('SAVE100'), ('NEW_USER'), ('NEWUSER');SELECT code FROM coupons WHERE code LIKE '%10\%';SELECT code FROM coupons WHERE code LIKE 'NEW\_%';

LIKE vs REGEXP

For more complex patterns MySQL has REGEXP (also written RLIKE), which uses regular expressions:

SELECT customer_nameFROM customersWHERE customer_name REGEXP '^[A-F]';

Performance tip

A pattern with a fixed start, such as 'R%', can use an index on the column. A leading wildcard, '%son', cannot: MySQL must check every row. On big tables, consider a FULLTEXT index for word searches.

SELECT product_nameFROM productsWHERE product_name LIKE '%Pack' OR product_name LIKE '%Pen%';

SELECT first_name, last_nameFROM employeesWHERE last_name LIKE '_a%';

LIKE operator FAQ