The LIKE operator in SQL is a powerful tool used to search for specified patterns within a column. It is particularly useful when you need to find data that matches a particular pattern, allowing for more flexible and dynamic queries compared to exact matches using the equality operator (=). This guide will provide an in-depth look at the LIKE operator, including its syntax, usage, and practical examples.
Syntax
The basic syntax for the LIKE operator is as follows:
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
Wildcards Used with LIKE
The LIKE operator is often used with two wildcards:
-
%(Percent): Represents zero, one, or multiple characters. -
_(Underscore): Represents a single character.
These wildcards allow for versatile pattern matching. Let's delve into their usage with detailed examples.
Using % Wildcard
The % wildcard matches any sequence of characters (including zero characters).
Examples:
- Match any string that starts with 'J':
SELECT * FROM employees WHERE name LIKE 'J%';
-- Expected output: id | name | department | salary | country
-- -------------------------------------------
-- 1 | John | Sales | 30000 | Nigeria
-- 7 | Johnson| IT | 80000 | Togo
-- 15 | Jody | IT | 47000 | Nigeria
- Match any string that ends with 'n':
SELECT * FROM employees WHERE name LIKE '%n';
-- Expected output: id | name | department | salary | country
-- -------------------------------------------
-- 1 | John | Sales | 30000 | Nigeria
-- 7 | Johnson| IT | 80000 | Togo
-- 14 | Ryan | IT | 55000 | Cameroun
- Match any string that contains 'ar':
SELECT * FROM employees WHERE name LIKE '%ar%';
-- Expected output: id | name | department | salary | country
-- -------------------------------------------
-- 3 | Carol | Marketing | 70000 | Togo
-- 11 | Mary | Sales | 40000 | Ghana
-- 12 | Helen | HR | 65000 | Cameroun
-- 13 | Maria | IT | 62000 | Togo
- Match any string that contains 'a' as the second character:
SELECT * FROM employees WHERE name LIKE '_a%';
-- Expected output: id | name | department | salary | country
-- -------------------------------------------
-- 2 | Alice | HR | 50000 | Ghana
-- 3 | Carol | Marketing | 70000 | Togo
-- 11 | Mary | Sales | 40000 | Ghana
Using _ Wildcard
The _ wildcard matches exactly one character.
Examples:
- Match names that start with any character followed by 'ohn':
SELECT * FROM employees WHERE name LIKE '_ohn';
-- Expected output: id | name | department | salary | country
-- -----------------------------------------
-- 1 | John | Sales | 30000 | Nigeria
-- 10 | Aohn | IT | 45000 | Nigeria
- Match names that have 'e' as the third character:
SELECT * FROM employees WHERE name LIKE '__e%';
-- Expected output: id | name | department | salary | country
-- -------------------------------------------
-- 4 | Steve | IT | 60000 | Cameroun
-- 12 | Helen | HR | 65000 | Cameroun
- Match names that have exactly 5 characters, with 'a' as the second character:
SELECT * FROM employees WHERE name LIKE '_a___';
-- Expected output: id | name | department | salary | country
-- -------------------------------------------
-- 3 | Carol | Marketing | 70000 | Togo
-- 13 | Maria | IT | 62000 | Togo
- Match names where the fourth character is 'n':
SELECT * FROM employees WHERE name LIKE '___n%';
-- Expected output: id | name | department | salary | country
-- -------------------------------------------
-- 1 | John | Sales | 30000 | Nigeria
-- 14 | Ryan | IT | 55000 | Cameroun
Combining % and _ Wildcards
You can combine the % and _ wildcards to create more complex search patterns.
Examples:
- Match names that start with 'J' and have 'o' as the third character:
SELECT * FROM employees WHERE name LIKE 'J_o%';
-- Expected output: id | name | department | salary | country
-- -------------------------------------------
-- 1 | John | Sales | 30000 | Nigeria
-- 15 | Jody | IT | 47000 | Nigeria
- Match names that contain 'a' followed by exactly one character and then 'e':
SELECT * FROM employees WHERE name LIKE '%a_e%';
-- Expected output: id | name | department | salary | country
-- -------------------------------------------
-- 12 | Helen | HR | 65000 | Cameroun
-- 13 | Maria | IT | 62000 | Togo
Practical Use Case
Suppose you have an employees table with the following data:
| id | name | department | salary | country |
|---|---|---|---|---|
| 1 | John | Sales | 30000 | Nigeria |
| 2 | Alice | HR | 50000 | Ghana |
| 3 | Carol | Marketing | 70000 | Togo |
| 4 | Steve | IT | 60000 | Cameroun |
| 5 | Dave | Sales | 60000 | Nigeria |
| 6 | Emma | HR | 55000 | Ghana |
| 7 | Johnson | IT | 80000 | Togo |
| 8 | Andrew | Marketing | 55000 | Cameroun |
| 9 | Jane | Marketing | 60000 | Togo |
| 10 | Aohn | IT | 45000 | Nigeria |
| 11 | Mary | Sales | 40000 | Ghana |
| 12 | Helen | HR | 65000 | Cameroun |
| 13 | Maria | IT | 62000 | Togo |
| 14 | Ryan | IT | 55000 | Cameroun |
| 15 | Jody | IT | 47000 | Nigeria |
Example Queries and Outputs
- Match names that start with 'J':
SELECT * FROM employees WHERE name LIKE 'J%';
-- Expected output: id | name | department | salary | country
-- -------------------------------------------
-- 1 | John | Sales | 30000 | Nigeria
-- 7 | Johnson| IT | 80000 | Togo
-- 15 | Jody | IT | 47000 | Nigeria
- Match names that contain 'ar':
SELECT * FROM employees WHERE name LIKE '%ar%';
-- Expected output: id | name | department | salary | country
-- -------------------------------------------
-- 3 | Carol | Marketing | 70000 | Togo
-- 11 | Mary | Sales | 40000 | Ghana
-- 12 | Helen | HR | 65000 | Cameroun
-- 13 | Maria | IT | 62000 | Togo
- Match names where the fourth character is 'n':
SELECT * FROM employees WHERE name LIKE '___n%';
-- Expected output: id | name | department | salary | country
-- -------------------------------------------
-- 1 | John | Sales | 30000 | Nigeria
-- 14 | Ryan | IT | 55000 | Cameroun
- Match names that start with any character followed by 'ohn':
SELECT * FROM employees WHERE name LIKE '_ohn';
-- Expected output: id | name | department | salary | country
-- -----------------------------------------
-- 1 | John | Sales | 30000 | Nigeria
-- 10 | Aohn | IT | 45000 | Nigeria
- Match names that have 'e' as the third character:
SELECT * FROM employees WHERE name LIKE '__e%';
-- Expected output: id | name | department | salary
| country
-- -------------------------------------------
-- 4 | Steve | IT | 60000 | Cameroun
-- 12 | Helen | HR | 65000 | Cameroun
- Match names that have exactly 5 characters, with 'a' as the second character:
SELECT * FROM employees WHERE name LIKE '_a___';
-- Expected output: id | name | department | salary | country
-- -------------------------------------------
-- 3 | Carol | Marketing | 70000 | Togo
-- 13 | Maria | IT | 62000 | Togo
Conclusion
The LIKE operator is an essential tool in SQL for performing pattern matching. By utilizing the % and _ wildcards, you can create complex queries that allow you to find data that meets specific criteria. Understanding how to use these wildcards effectively can significantly enhance your ability to retrieve and analyze data from your databases.
Check out this article I wrote on the Advanced Use-Cases and Complex Queries with SQL LIKE Operator.
PS:
I love coffee, and writing these articles takes a lot of it! If you enjoy my content and would like to support my work, you can buy me a cup of coffee. Your support helps me to keep writing great content and stay energized. Thank you for your kindness!
Buy Me A Coffee.
Top comments (0)