Design-patternsPHPJavaScriptEcommerceMySQL

MySQL LIKE operator

Tipo IT - author
Posted by : Darko Borojevic - contact me | go home

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 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: 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

mysql wildcards

Result

mysql wildcards This wildcard selects only employees whose last names end with the letter n.

Query statement

mysql wildcards

Result

mysql wildcards 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 substring:

Query statement

mysql wildcards

Result

mysql wildcards 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 ANDoperator here. With that said we define the pattern that we need as follows:

Query statement

mysql-like-and-wildcards

Result

mysql-like-and-wildcards

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:

Query statement

mysql like and not operators

Result

mysql like and not operators We can see in the result table that last names like Bondur, or Bow, are left out.





Print article

Email article

Do you have a comment?