Using Wildcards In MySQL LIKE Clause – With Examples

By /

Using wildcards in the LIKE clause of a MySQL query can save a lot of effort when you write a query that looks for a pattern in a character string.

Just as a refresher, the standard wildcard characters are:

% substitute for any characters at the BEGINING or END of the string.
_  substitute for any single character.

MySQL LIKE Wildcard Examples

Look for customers with a last name that begins with “A”, followed by any four characters and “N”, followed by any characters


SELECT Customer_ID, Customer_Last_Name
FROM Customers
WHERE Customer_Last_Name LIKE 'A___n%';

Customer_ID    Customer_Last_Name
14	        Antonella Muse
23	        Alain Fishborn
26	        Arian Aspin

Look for customer last names that do not begin with “A”


SELECT Customer_ID, Customer_Last_Name
FROM Customers
WHERE Customer_Last_Name NOT LIKE 'A%';

CustomerID    Customer_Last_Name
13	        Aida Yespica
20	        Patrick Swayze

Look for customer last names that start with “M” and also contain “a” or “e”


SELECT Customer_ID, Customer_Last_Name
FROM Customers
WHERE Customer_Last_Name LIKE 'M%' AND
(
Customer_Last_Name LIKE '%a%' OR Customer_Last_Name LIKE '%e%'
);

Customer_ID	Customer_Last_Name
21	          Marlon Zeana

Look for customer last names that have a length of 11 characters exactly

SELECT Customer_ID, Customer_Last_Name
FROM Customers
WHERE Customer_Last_Name LIKE '___________';

Customer_ID    Customer_Last_Name
26	        Arian Aspin

🚨🚨 NOTE: The previous example is one of those “Just because you can, doesn’t mean you should” kinda things.

It’s a pain in the ass to count the underscores every time you’re trying to figure out the intent of the SQL query.

It’s much more intuitive to write the query using the LENGTH function:

SELECT Customer_ID, Customer_Last_Name
FROM Customers
WHERE LENGTH(Customer_Last_Name) = 11;

Customer_ID    Customer_Last_Name
26	        Arian Aspin
Scroll to Top