How to use LIKE in SQL

In this SQL article, we will learn and understand how to use LIKE to the columns in the SQL tables.

What is Like?

Like is an operator in the SQL. It is used to find a particular pattern matching records in each row of the field. The Like operator is always used with the WHERE clause in the SQL queries. We can use the Like operator with the SELECT query, the UPDATE query and the DELETE query.

The Syntax for using the Like operator in Structured Query Language:

SELECT columnname1, columnname2, columnname3 FROM tablename WHERE columname LIKE pattern;

In the Structured Query Language, the LIKE operator is used with the following two wildcard characters:

1 Percent Sign (%)

2 Underscore Sign (_)

1 Percent Sign (%):

This character is used to match zero, one, or more than one character in the column.

Syntax of using percent sign with the LIKE operator:

1 The following syntax matches all the values whether its string value or numeric which starts with the character or numeric:

SELECT columnname1, columnname2, columnname3 FROM tablename WHERE columnaname LIKE ‘character%’;

2 The following syntax matches all the values whether its string value or numeric which ends with the character or numeric:

SELECT columnname1, columnname2, columnname3 FROM tablename WHERE columnaname LIKE ‘%character’;

3 The following syntax matches all the values whether its string value or numeric which starts with the character or numeric and ends with characters or numeric:

SELECT columnname1, columnname2, columnname3 FROM tablename WHERE columnaname LIKE ‘starting_character% ending_character’;

4 The following syntax matches all the values whether its string value or numeric which contains the character or numeric at any position:

SELECT columnname1, columnname2, columnname3 FROM tablename WHERE columnaname LIKE ‘%character%’;

2 Underscore Sign (_):

This character is used to match one or a single character.

Syntax of using underscores sign with the LIKE operator:

1 The following syntax matches all the strings or numeric which contains 5 characters and starts with any character:

SELECT columnname1, columnname2, columnname3 FROM tablename WHERE columnaname LIKE ‘character____%’;

2 The following syntax matches all the strings or numeric which contains characters at 3 positions:

SELECT columnname1, columnname2, columnname3 FROM tablename WHERE columnaname LIKE ‘__character%’;

To use SQL Like operator to the column in the table, we have to follow the following steps in the sequence:

Step 1: Create a database or use an existing database.

Step 2: Create the table in the database and insert the values into the table or use the existing table from the selected database.

Step 3: View the inserted records from the table.

Step 4: Now we can use the SQL Like operator to the column in the table.

Now, we will understand the SQL operator with the help of examples.

Step 1: Create a database or use an existing database:

We have already created database name Company:

To use a database we should fire USE query followed by the database name.

Step 2: Create the table and insert the records into the newly created table or use an existing created table. We have created table named Employee.

Step 3: View the records from the table, to view we use the SELECT query followed by table name.

Table: Employee:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4

Step 4: Now we can use the SQL Like operator.

Let’s understand the examples with percent sign:

Example 1: Write a query to display the employee id, first name and city from employee table whose employee first name starts with ‘A’.

SELECT employeeid, first_name, city FROM employee WHERE first_name LIKE 'A%';

From the above query, we displayed the employee id, first name and the city name from employee table of that employee whose first name starts with the character A in the table.

Output:

EMPLOYEEID	FIRST_NAME	CITY
3002	ANUJA	JAIPUR
4002	ASHWINI	NOIDA
5001	ARCHIT	DELHI

As shown in the above output, the result contains only those employees whose name starts with ‘A’.

Example 2: Write a query to display the employee id, first name, last name and the city from employee table whose employee city name ends with ‘I’.

SELECT employeeid, first_name, last_name, city FROM employee WHERE city LIKE '%I';

From the above query, we displayed the employee id, first name, last name and the city name from employee table of that employee whose city name ends with the character ‘I’ in the table.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	CITY
2003	RUCHIKA	JAIN	MUMBAI
3003	DEEPAM	JAUHARI	MUMBAI
4001	RAJESH	GOUD	MUMBAI
4003	RUCHIKA	AGARWAL	DELHI
5001	ARCHIT	SHARMA	DELHI

As shown in the above output, the result contains only those employees whose city name ends with ‘I’.

Example 3: Write a query to display the employee id, first name, last name and the department from employee table whose employee first name starts with ‘P’ and ends with ‘I’.

SELECT employeeid, first_name, last_name, department FROM employee WHERE first_name LIKE 'P%I';

From the above query, we displayed the employee id, first name, last name and the department name from employee table of that employee whose first name starts with the character ‘P’ and ends with character ‘I’ in the table.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	DEPARTMENT
2001	PRACHI	SHARMA	ORACLE
3001	PRANOTI	SHENDE	JAVA

As shown in the above output, the result contains only those employees whose first name ends with ‘P’ and ends with ‘I’ in the table.

Example 4: Write a query to display the employee id, first name, last name, salary and the department from employee table whose employee salary contains ‘5’ at any index position 

SELECT employeeid, first_name, last_name,salary, department FROM employee WHERE salary LIKE '%5%';

From the above query, we displayed the employee id, first name, last name, salary and the department name from employee table of that employee whose salary contains ‘5’ number at any index position between starting index and ending index in the table.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	DEPARTMENT
1001	VAIBHAVI	MISHRA	65500	ORACLE
1003	NIKHIL	VANI	50500	FMW
2001	PRACHI	SHARMA	55500	ORACLE
2002	BHAVESH	JAIN	65500	FMW
2003	RUCHIKA	JAIN	50000	C#
3001	PRANOTI	SHENDE	55500	JAVA
3002	ANUJA	WANRE	50500	FMW
3003	DEEPAM	JAUHARI	58500	JAVA
4001	RAJESH	GOUD	60500	TESTING
4002	ASHWINI	BAGHAT	54500	JAVA
5001	ARCHIT	SHARMA	55500	TESTING

As show in the output, the result contains only that employee whose salary includes 5 at any index position like at 2^nd position, 3^rd position or 4^th position.

Let’s understand the examples with underscore sign:

Consider the employee table with following records:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4

Example 1: Write a query to display the entire employee table whose employee salary starts with number ‘5’ and contains only five numbers.

SELECT * FROM employee WHERE salary LIKE '5____';

From the above query, we display the entire table of employee where employee salary starts with number 5 and contains only five number salaries in the table.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4

As show in the output, the result contains only those employees’ records whose salary starts with numeric 5 and contains only 5 number salaries.

Example 2: Write a query to display the entire employee table whose employee city contains U at the second position.

SELECT * FROM employee WHERE city LIKE '_U%';

From the above query, we display the entire table of employee where employees’ city contains U at the second position in the table.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4

As show in the output, the result contains only those employees’ records whose city contains U at the second position.

Example 3: Write a query to display the entire employee table whose employee department contains J at the first position and V at the third position.

SELECT * FROM employee WHERE Department LIKE 'J_V%';

From the above query, we display the entire table of employee where employees’ department contains J at the first position and V at the third position in the table.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3

As show in the output, the result contains only those employees’ records whose department contains J at the first position and V at the third position.

Example 4: Write a query to display the entire employee table whose employee salary contains 5 at the third position.

SELECT * FROM employee WHERE salary LIKE '__5%';

From the above query, we display the entire table of employee where employees’ salary contains 5 at the third position in the table.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4

As show in the output, the result contains only those employees’ records whose salary contains 5 at the third position.

We can use multiple LIKE operator in the single SQL query using AND operator and OR operator.

Example 1: Write a query to display the entire employee table whose employee salary contains ‘5’ at the third position or city name ends with ‘I’.

SELECT * FROM employee WHERE salary LIKE '__5%' OR city LIKE '%I';

From the above query, we display the entire table of employee where employees’ salary contains 5 at the third position or city name ends with the character ‘I’ in the table. Here we have used multiple LIKE operator using OR operator between this LIKE operator.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4

As show in the output, the result contains only those employees’ records whose salary contains 5 at the third position or city name ends with the character ‘I’.

Example 2: Write a query to display the entire employee table whose employee first name starts with ‘P’ and department ends with ‘E’.

SELECT * FROM employee WHERE first_name LIKE 'P%' AND department LIKE '%E';

From the above query, we display the entire table of employee where employees’ first name starts with ‘P’ AND department name ends with the character ‘E’ in the table. Here we have used multiple LIKE operator using AND operator between this LIKE operator.

Output:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1

As show in the output, the result contains only those employees’ records whose first name starts with ‘P’ AND department name ends with the character ‘E’.