MySQL LIKE Operator

The MySQL LIKE Operator is used to perform a wild search on tables. The Like operator uses Wildcards to extract the records matching the specified pattern.

For instance, If you forgot the Product spelling or description, then use the MySQL LIKE operator Wildcard to find the matching records. The following are the supporting Wildcards.

Wildcards	Description
%	Use this to match Zero or more characters.
_	Use this to match one character exactly.

Before we get into the Wildcards in LIKE Operator example, let me show you the syntax.

MySQL Like Operator Syntax

The syntax behind this Wildcard is:

SELECT Columns
FROM Table
WHERE Column_Name LIKE Wildcard_Expression ESCAPE 'escape_charcater'

The wildcard expression might contains % or _. If you want to escape any special character, then use default \ or any character. If you use other than \, then specify that character safer the ESCAPE keyword. For this demonstration, We are going to use the below-shown data.

MySQL Like Operator Wildcard % Example

The Wildcard % (percentage) represents zero or more characters. For example, the below % wildcard query returns all the Employees whose First name starts with Letter R.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE `First Name` LIKE 'R%';

This wildcard query example displays the Employees whose Occupation ends with l Letter.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE Occupation LIKE '%l';

Multiple Values Example 2

The following Like multiple values query selects the Employees Whose Qualification starts with Letter B and ends with s.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE Qualification LIKE 'B%s';

This query of Multiple values example returns the Customers Whose Qualification contains d and e in any position.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM customer
WHERE Qualification LIKE '%d%e';

MySQL Like Wildcard _ Operator Example

The Wildcard Underscore sign (_) represents a single character. For instance, the following query selects the employees Whose Qualification includes the second letter as a.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM customer
WHERE Qualification LIKE '_a%';

This query displays the Customers whose Qualification contains the second letter is a and t as a fourth letter.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM customer
WHERE Qualification LIKE '_a_t%';

The below query displays the Employees whose Last name has at least six characters.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM customer
WHERE  `Last Name` LIKE N'_%_%_%_%_%_%';

Like Escape Example

Until now, we have seen wildcards to display one or more characters. However, if your column has some special characters such as _ or % or $, then you have to use the ESCAPE character. For this, We are going to use the below-shown data.

As you can see from the above screenshot, our data had _ and % symbols in the First Name column. In MySQL Like operator, \ is the default escape character to escape any special characters.

For example, the following query uses \ to escape _ from the first name.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM customer
WHERE FirstName LIKE '%\_%';

You can also use your own character as the escape character by using the ESCAPE keyword. The below shown Like escape query uses # as the escape character to skip the _ symbol.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
FROM customer
WHERE FirstName LIKE '%#_%' ESCAPE '#';

This time we used $ as an escape character to escape @ symbol in the Email Address column.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM customer
 WHERE EmailAddress LIKE '%$@%' ESCAPE '$';

The following query escapes the % symbol in the First name.

SELECT CustomerKey,
       FirstName,
       LastName,
       BirthDate,
       EmailAddress,
       `Yearly Income`,
       Profession
 FROM customer12
 WHERE FirstName LIKE '%\%%';

Command Prompt Example

It is just an example to show you the Like operator in the command prompt.