SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAC#5
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4

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:

EMPLOYEEIDFIRST_NAMECITY
3002ANUJAJAIPUR
4002ASHWININOIDA
5001ARCHITDELHI
How To Use LIKE In SQL

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:

EMPLOYEEIDFIRST_NAMELAST_NAMECITY
2003RUCHIKAJAINMUMBAI
3003DEEPAMJAUHARIMUMBAI
4001RAJESHGOUDMUMBAI
4003RUCHIKAAGARWALDELHI
5001ARCHITSHARMADELHI
How To Use LIKE In SQL

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:

EMPLOYEEIDFIRST_NAMELAST_NAMEDEPARTMENT
2001PRACHISHARMAORACLE
3001PRANOTISHENDEJAVA
How To Use LIKE In SQL

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYDEPARTMENT
1001VAIBHAVIMISHRA65500ORACLE
1003NIKHILVANI50500FMW
2001PRACHISHARMA55500ORACLE
2002BHAVESHJAIN65500FMW
2003RUCHIKAJAIN50000C#
3001PRANOTISHENDE55500JAVA
3002ANUJAWANRE50500FMW
3003DEEPAMJAUHARI58500JAVA
4001RAJESHGOUD60500TESTING
4002ASHWINIBAGHAT54500JAVA
5001ARCHITSHARMA55500TESTING
How To Use LIKE In SQL

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAC#5
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4002ASHWINIBAGHAT54500NOIDAJAVA3
5001ARCHITSHARMA55500DELHITESTING4
How To Use LIKE In SQL

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
How To Use LIKE In SQL

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
3001PRANOTISHENDE55500PUNEJAVA3
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4002ASHWINIBAGHAT54500NOIDAJAVA3
How To Use LIKE In SQL

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
5001ARCHITSHARMA55500DELHITESTING4
How To Use LIKE In SQL

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4
How To Use LIKE In SQL

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
2001PRACHISHARMA55500CHANDIGARHORACLE1
How To Use LIKE In SQL

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’.



ADVERTISEMENT
ADVERTISEMENT