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 2nd position, 3rd position or 4th 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’.