It is used for searching a string or a sub-string to find a certain character or group of characters from a string. We can use the LIKE Operator of SQL to search sub-strings. The LIKE operator is used with the WHERE Clause to search a pattern in a string of columns. The LIKE operator is used in conjunction with the two wildcard characters.
- Percentage sign( % ): It represents zero, one, or multiple characters of variable length.
- Underscore ( _ ): It represents one, single character of fixed length.
The Syntax of a LIKE Clause is
SELECT Column1, Column2 FROM TableName WHERE Column LIKE [Expression];
In this example, we will create a schema for our database and name it Emp_details. After that, we will create a table inside it with the name Emp_data and try to search for a sub-string from the table’s data.
Step 1: Create a database
In order to create a database we need to use the CREATE operator.
CREATE DATABASE Emp_details;
Step 2: Create a table inside the database
In this step, we will create the table Emp_data inside the Emp_details database.
CREATE TABLE Emp_data(id INT,
Step 3: Insert data into the table
In order to insert the data inside the database we need to use INSERT operator.
INSERT INTO Emp_data (id, first_name, last_name, Salary,Age)
VALUES (1, "Yash", "Kumar", 30000,25),
(2, "Rahul", "Yadav", 40000,22),
(3, "Mohit", "Kumar", 50000,21),
(4, "Ritik", "Kumar", 30000,23),
(5, "Shubham", "Pal", 20000,25);
Using LIKE Clause with % to match any number of Characters
Example 1: To fetch records from the Emp_data Table with first_name starting with the letter ‘Y%’.
SELECT * FROM Emp_data WHERE first_name LIKE 'Y%';
Example 2: To fetch records from the Emp_data Table with first_name ending with the letter ‘%m’.
SELECT * FROM Emp_data WHERE first_name LIKE '%m';
Example 3: To fetch records from the Emp_data with first_name with the letter ‘h’ at any position.
SELECT * FROM Emp_data WHERE first_name LIKE '%h%';
To fetch the records from Emp_data in which salary contains a number 50 in between.
SELECT * FROM Emp_data WHERE salary LIKE '%50%';
Using LIKE Clause with _ to match only one Character
Example 1: To fetch records from the Emp_data Table with first_name ending any letter but starting from ‘R’.
SELECT * FROM Emp_data WHERE first_name LIKE 'R____';
Example 2: To fetch a records from Emp_data table in which salary is starting with ‘3’ succeeding any two digits and finally ends with ’00’.
SELECT * FROM Emp_data WHERE Salary LIKE '3__00';
Example 3: To fetch records from the Emp_data Table with last_name starting with ‘K’.
SELECT * FROM Emp_data WHERE last_name LIKE 'K____';
To match any part of the string in SQL, we can use the LIKE operator with a wildcard. Typically, there are two types of wildcard operators utilized in SQL.
- %(percentage): It can represent either zero, one, or multiple characters with a variable length.
- _ (underscore): It is used to match only single character of a fixed length.
Unlock the Power of Placement Preparation!
Feeling lost in OS, DBMS, CN, SQL, and DSA chaos? Our Complete Interview Preparation
Course is the ultimate guide to conquer placements. Trusted by over 100,000+ geeks, this course is your roadmap to interview triumph.
Ready to dive in? Explore our Free Demo Content and join our Complete Interview Preparation