LIKE Operator in MySQL

LIKE Operator in MySQL with Examples

In this article, I am going to discuss Like Operator in MySQL with Examples. Please read our previous article where we discussed Logical AND, OR & NOT Operators in MySQL. At the end of this article, you will understand LIKE, NOT LIKE operators along with the Wildcard Characters available in MySQL with examples.

What is LIKE Operator in MySQL?

The LIKE Operator in MySQL is basically used with the WHERE clause to search for a specific pattern in a string. The string pattern contains wildcard characters that represent missing characters. The LIKE operator is written as a word “LIKE” followed by a pattern to match with column values. Here is an example of LIKE operators in SQL statements,

SELECT * FROM Employee WHERE name LIKE ‘%P’; Here the percentage sign is a wildcard character.

Syntax: Expression LIKE pattern [ESCAPE ‘escape character’], where expression is the specific column of the table, the pattern is the character expression that contains pattern matching and escape_character is used to check the wildcard characters like % or _.

Understanding LIKE Operator in MySQL:

Let us understand LIKE Operator in MySQL with Examples. We are going to use the following Employee table to understand the LIKE Operator.

Understanding LIKE Operator in MySQL

Please use the following SQL Script to create the company database and employee table with the required records.

CREATE DATABASE company;
USE company;

CREATE TABLE employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(45) NOT NULL,
  Department VARCHAR(45) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(45) NOT NULL,
  Age INT NOT NULL,
  City VARCHAR(45) NOT NULL
);

INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1001, 'John Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1002, 'Mary Smith', 'HR', 45000, 'Female', 27, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1003, 'James Brown', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda Jones', 'HR', 75000, 'Female', 26, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag Mohanty', 'IT', 35000, 'Male', 25, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit Mohanty', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya Kumar', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1011, 'Pramod Panda', 'IT', 45000, 'Male', 27, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1012, 'Preety Tiwary', 'HR', 55000, 'Female', 28, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1013, 'Santosh Dube', 'IT', 52000, 'Male', 28, 'Mumbai');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1014, 'Sara Talour', 'HR', 85000, 'Female', 26, 'London');
INSERT INTO employee (Id, `Name`, Department, Salary, Gender, Age, City) VALUES (1015, 'Pamela Kar', 'Finance', 70000, 'Female', 26, 'London');
Understanding MySQL Wildcard Characters

Before using the LIKE Operator, first, we need to understand wildcard characters. A wildcard character in MySQL is used as a substitute for any other characters in a string. The wildcards are used to search for data in a given column. The wildcard characters in MySQL are basically used with the LIKE operator. In MySQL, there are two wildcards.

  1. % symbol represents any no of characters (zero or more characters) in the expression.
  2. _ will represent a single character in the expression.
Like Operator Using % in MySQL with Examples

The percent (%) means it will search for zero or more characters after the “%”. Here ‘%’ is the wildcard character that we will use before or after characters to search for the required matched string of characters.

Example: Fetch all the employees whose names start with P.

The following SQL query will return all employees from the employee table whose name starts with the character ‘P’ followed by any string of characters. 

SELECT * FROM Employee WHERE Name LIKE ‘P%’;

Once you execute the above SQL Query, you will get the following result set, and please observe all the employees having the name start with the character P.

Like Operator Using % in MySQL with Examples

Example: Fetch all the employees whose name ends with ar.

The following SQL statement will return all the employees from the employee table whose name ends with the string ‘ar’. Here we mentioned the pattern as ‘%ar’. This means return all records whose name ends with the string ‘ar’.

SELECT * FROM Employee WHERE Name LIKE ‘%ar’;

Once you execute the above SQL Statement, then you will get the following result set and please observe all the employees having the name ends with the string ar.

Like Operator Using % in MySQL with Examples

Example: Fetch all employee whose name contains the word am

The following SQL statement will return all the employees from the employee table where the Name containing the word ‘am’ anywhere in the name column. This is because we have mentioned patterns like ‘%am%’. 

SELECT * FROM Employee WHERE Name LIKE ‘%am%’;

Once you execute the above SQL query, you will get the following result set.

LIKE Operator in MySQL with Examples

Using NOT LIKE Operator in MySQL

We can also revert the SQL statement by using the NOT LIKE operator. Let us understand the use of NOT Like Operator with some examples.

Example: Fetch all the employees whose name does not start with P.

The following SQL query will return all employees whose name does not start with the character ‘P’. The employee whose starting alphabet is not P will be returning as output. This is just the opposite of the LIKE Operator.

SELECT * FROM Employee WHERE Name NOT LIKE ‘P%’;

Once you execute the above query, you will get the following result set, and please observe it return all the employees whose is not start with the character p.

Using NOT LIKE Operator in MySQL

Example: Fetch all the employees whose name does not end with ar.

The following SQL query will return all the employees whose name does not end with the word ‘ar’.

SELECT * FROM Employee WHERE Name NOT LIKE ‘%ar’;

Once you execute the above SQL Statement, you will get the following result set which returns all the employees whose name does not end with the string ar.

Fetch all the employees whose name does not end with ar

Example: Fetch all employee whose name does not contain the word am

The following SQL query will return all the employees from the employee table where the Name does not contain the word ‘am’ anywhere in the name column.

SELECT * FROM Employee WHERE Name NOT LIKE ‘%am%’;

Once you execute the above SQL query, you will get the following result set.

Fetch all employee whose name does not contain the word am

Like Operator Using _ (underscore) Wildcard Character in MySQL

The underscore (_) means it will exactly search for one character after the “_”. Let us understand the use of _ wildcard character with an example. Let’s filter the data more precisely and this time get the employee’s city starting with ‘MUMBA’ but only containing a total of 6 letters. The following is the SQL statement

SELECT * FROM Employee WHERE City LIKE ‘MUMBA_’;

Once you execute the above query, you will get the following output. Here the employees with city value contain MUMBA and are of only 6 letters are returned in the result set.

Like Operator Using _ (underscore) Wildcard Character

Example: Fetch all the employee whose department is having two letters

The following SQL query will return all the employees from the employee table where the Department name is having two characters. Here we are using two underscores (__) as the part in the LIKE Operator.

SELECT * FROM Employee WHERE Department LIKE ‘__’;

Once you execute the above SQL Statement, you will get the following result set which includes only the employees from the IT and HR departments.

Like Operator Using _ (underscore) Wildcard Character in MySQL

In the next article, I am going to discuss IN and BETWEEN Operator in MySQL with Examples. Here, in this article, I try to explain LIKE Operator in MySQL with Examples. I hope you enjoy this LIKE Operator in MySQL article.

Leave a Reply

Your email address will not be published. Required fields are marked *