MySQL LIKE Operator

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

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

In this article, we will show you, How to use the Wildcards in MySQL LIKE Operator with multiple examples. Before we get into the example, let me show you the syntax.

MySQL Like Operator Syntax

The syntax behind this MySQL 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 Wildcard % Example

The MySQL 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 `MySQL Tutorial`.customer
 WHERE `First Name` LIKE 'R%';

OUTPUT

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

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

OUTPUT

MySQL Like Multiple Values Example 2

The following 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 `MySQL Tutorial`.customer
 WHERE Qualification LIKE 'B%s';

OUTPUT

This MySQL Like query Multiple values example returns the Customers Whose Qualification contains d and e at any position.

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

OUTPUT

MySQL Like Wildcard _ Example

The MySQL Like Wildcard Underscore sign (_) represents a Single character. For instance, the following query select the employees Whose Qualification contains a as Second Letter.

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

OUTPUT

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

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

OUTPUT

The below MySQL Like wildcard query display the Employees whose Last name has at least six characters.

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

OUTPUT

MySQL Like Escape Example

Until now, we have seen the wildcards to display one or more characters. However, if your column has some special characters like _ 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 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 first name.

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

OUTPUT

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

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

OUTPUT

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

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       Sales,
       HireDate
 FROM `MySQL Tutorial`.customer
 WHERE EmailAddress LIKE '%[email protected]%' ESCAPE '$';

OUTPUT

The following MySQL Like operator query escapes % symbol in First name.

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

OUTPUT

MySQL Like Operator – Command Prompt Example

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

SELECT 'a' LIKE 'a', 'a' LIKE ' a', 'a' LIKE 'a ', 'a' = 'a ';

SELECT 'a' LIKE 'a', 'a' LIKE ' a', 'a' LIKE 'a ';

SELECT '[email protected]' LIKE 'Suresh_', '[email protected]' LIKE '%h_';

OUTPUT

Thank You for Visiting Our Blog