MySQL LIKE operator
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. MySQL provides us with two specific wildcard characters for building patterns and these are percentage (%) and underscore (_). The percentage wildcard will match any string of zero or more characters, and the underscore wildcard will match any single character. In SQL we have a WHERE clause that we can combine with the = symbol that works fine for database queries where we need to perform an exact match. For example we can have a statement like this: SELECT * FROM employees WHERE lastName = 'Murphy';. However, sometimes we wish to find all the results where lastName should contain string phy, for example. This can be done by using the LIKE 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.
Query statement
Result
This wildcard selects only employees whose last names end with the letter n.
Query statement
Result
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 an sub-string:
Query statement
Result
We can see this is working out for us perfectly. Let's go on.
2) LIKE and AND operators with the percentage wildcard
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 AND operator here. With that said we define the pattern that we need as follows:
Query statement
Result
3) LIKE and NOT operators with the percentage wildcard
We can also combine LIKE with NOT operator in SQL, to find a sub-string 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:
Query statement
Result
We can see in the result table that last names like Bondur, or Bow, are left out. At the end of this post let us look at this example of LIKE operator with the underscore wildcard. This wildcard is used for looking at specific single characters:
4) LIKE operator with the underscore wildcard
Query statement
Result
This is a query that says "give me all the first names that start with J, ends with ff, and contain one character in between". This first name in our result is Jeff. As you can see, possibilities for using LIKE operator in your SQL queries as a part of your app's back-end are numerous. Happy coding!
