MySQL LIKE operator
MySQL LIKE operator with wildcards - examplesIn 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
WHEREclause 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
lastNameshould contain string phy, for example. This can be done by using the
LIKEoperator 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 (%) wildcardThis wildcard that selects only employees whose first names start with letter L, and are followed by any number of subsequent characters.
ResultThis wildcard selects only employees whose last names end with the letter n.
ResultSometimes 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
ResultWe can see this is working out for us perfectly. Let's go on.
2) LIKE and AND operators with the percentage wildcardLet 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
ANDoperator here. With that said we define the pattern that we need as follows:
3) LIKE and NOT operators with the percentage wildcardWe 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:
ResultWe 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
ResultThis 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 backend are numerous. Happy coding!
Print article Email article