SQL Like
The LIKE is a logical operator in the Structured Query Language. This SQL operator is used in the WHERE clause with the following three statements:
- SELECT Statement
- UPDATE Statement
- DELETE Statement
– It filters the records from the columns based on the pattern specified in the SQL query.
– Following are two wildcard characters that are used either in conjunction or independently with the SQL LIKE operator:
- % (percent sign): This wildcard character matches zero, one, or more than one character.
- _ (underscore sign): This wildcard character matches only one or a single character.
Example:
SELECT column_Name1, column_Name2 …., column_NameN FROM table_Name WHERE column_name LIKE pattern;
In this syntax, the pattern is a sequence of characters that have to be searched in that column which is just specified after the WHERE clause.
Let’s take the following Employee table which helps you to analyze the LIKE operator with % sign:
Suppose, you want to filter the records of those employees whose names start with “S”. For this operation, you have to type the following query:
SELECT * FROM Employee WHERE Name LIKE ‘S%’;
This query shows the following table:
Suppose, you want to show the name and salary of those employees whose department name starts with “C” and ends with “g”. For this operation, you have to type the following query:
SELECT Name, Emp_Salary FROM Employee WHERE Emp_Dept LIKE ‘C%g’;
This query shows the following table in the output:
Suppose, you want to show all records of those employees from the above employee table whose Name contains the letter “a” in any position. For this operation, you have to type the following query:
SELECT * FROM Employee WHERE Emp_Dept LIKE ‘%a%’;
This query shows the following table in the output:
Let’s take the following Student table which helps you to analyze the LIKE operator with _ (underscore) sign:
Suppose, you want to show all records of those students whose Name contains “a” at the second position. For this operation, you have to type the following query with an underscore sign:
SELECT * FROM Student WHERE Name LIKE ‘_a%’;
This query shows the following table in the output:
Suppose, you want to access records of those students whose names contain at least 3 characters and start with the letter “S”. For this operation, you have to type the following query:
SELECT * FROM Student WHERE Name LIKE ‘S___%’;
In this query, you have to use the underscore sign three times after the S character. The above query shows the following table in the output:
Suppose, you want to access Roll_No, Name, and Marks of those students whose Marks are 2 digits long and end with ‘5’:
SELECT * FROM Student WHERE Name LIKE ‘_5’;
The above query shows the following table in the output:
Course Video
NOTE: Practice below questions on site editor.
1.Make a SQL Query to find name like ‘en’ from HumenResources_Department table
Table: (HumanResources_Department)(1 row count)
Output:
2. Write a SQL Query to find name From Person_ContactType table where name like ‘a___i’.
Table: (Person_ContactType)-(2 row count)
Output:
3. Write SQL Query to get productID, name, color, listprice, from Production_Product table where Name start with ‘B’ and end with ‘e’
Table: (Production_product) (1 row count)
Output:
4. Write SQL Query to get All the columns from Production_Product table where productNumber like
’CA-7457’
Table: (Production_Product) (row count 1)
Output:
5. Make a SQL Query to get all the columns from Sales_SalesReason table where name start with ‘R’ and end with ‘w’
Table: (Sales_SalesReason) ( row count 1)
Output:
6. Write a sql query to get employeeId, Lastname, HireDate from employees table where employeeId is like ‘N_n’.
HINT:’Use northwind’.
Output
7. Write a sql query to get all the column from customers table where company name is like ‘h,l,c’.
HINT: “use northwind”.
Output
8. Make a sql query to fetch any 4 column from Person.Address table where city start from ‘B’ & end from ‘L’.
HINT: ‘use adventure’.
OUTPUT:
9. Make a sql query to get any 2 column from person.person table where firstname like ‘D’;
OUTPUT:
10. Write a sql query to get all the column from production.product table
Where name end with ‘m’;
HINT: ‘Use adventure’
OUTPUT:
YouTube Reference :
1) SQL Like in Hindi/Urdu
2) SQL Like in English
The SQL LIKE operator is used to search for a specified pattern in a column. It is often used in conjunction with the WHERE clause to filter data.
The basic syntax is:
sql
Copy code
SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;
For example, to find names starting with “J”:
sql
Copy code
SELECT * FROM Employees WHERE name LIKE ‘J%’;
• %: Matches zero or more characters.
• _: Matches exactly one character.
For example:
• LIKE ‘Jo%’ finds “John” or “Joe”.
• LIKE ‘J_n’ finds “Jon” or “Jen”.
• LIKE: Used for pattern matching (e.g., LIKE ‘A%’).
• IN: Used to match specific values (e.g., IN (‘A’, ‘B’)).
Yes, you can use AND or OR to combine multiple LIKE conditions:
sql
Copy code
SELECT * FROM Employees
WHERE name LIKE ‘A%’ OR name LIKE ‘B%’;
Yes, you can find a detailed SQL LIKE Tutorial on our website, covering basics and advanced examples.
- Forgetting to use wildcards % or _ in patterns.
- Case sensitivity issues in some databases (e.g., MySQL is case-insensitive by default).
• Use LOWER() or UPPER() functions to make comparisons case-insensitive:
sql
Copy code
SELECT * FROM Employees
WHERE LOWER(name) LIKE ‘john%’;
Yes, but it treats numeric data as text for pattern matching. For example:
sql
Copy code
SELECT * FROM Orders WHERE order_id LIKE ‘12%’;
• Combine LIKE with other functions like CONCAT:
sql
Copy code
SELECT * FROM Employees
WHERE name LIKE CONCAT(‘A’, ‘%’);
• Use NOT LIKE to exclude patterns:
sql
Copy code
SELECT * FROM Employees WHERE name NOT LIKE ‘A%’;
