MySQL LIKE operator with wildcards - examples
In this post we will cover MySQL LIKE
operator and wildcards that can be used in synergy with LIKE to get specific query results. In SQL we have a
clause that we can combine with the =
symbol that works fine for database queries where we need to perform an exact match. For example:
SELECT * FROM employees WHERE lastName = 'Murphy';
. However, sometimes we wish to find all the results where
should contain string phy
, for example. This can be done by using the
operator along with the WHERE clause and some MySQL wildcards. It is worth mentioning that we can also use LIKE without the wildcards, but then this operator behaves pretty much the same as the equal operator. I will present a few examples of this using the classicmodels sample database
, so please go and download this zip file first, and install the database on your phpmyadmin panel in your localhost server. Let's look at some classic examples of using LIKE with different wildcard scenarios:
1) LIKE operator with the percentage (%) wildcard
This wildcard that selects only employees whose first names start with letter L, and are followed by any number of subsequent characters.
This wildcard selects only employees whose last names end with the letter n.
Sometimes we want to search the string which is contained somewhere inside of first name, or last name, or product name. In that case you can use the
wildcard at the beginning and at the end of the pattern. Let's try finding all employees whose last name contains
We can see this is working out for us perfectly. Let's go on.
2) LIKE and AND operators with wildcards
Let us make things a bit more complicated. Let's say we want to find all employees whose last names start with P
, and ends with letter n
. In between content can be any single character, which is not important to us in a query like this. For this purpose we use the
operator here. With that said we define the pattern that we need as follows:
3) LIKE and NOT operators with wildcards
We can also combine LIKE with NOT operator in SQL, to find a substring that does not match a predefined pattern. Let's perform a search for employees whose last names can't begin with the letter B
using NOT LIKE combination:
We can see in the result table that last names like Bondur, or Bow, are left out.