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.

How to Use the 'LIKE' Operator in SQL

Syntax

Wildcards Used with LIKE

Using % Wildcard

Using _ Wildcard

Combining % and _ Wildcards

Practical Use Case

Example Queries and Outputs

Conclusion

Wildcards Used with `LIKE`

Using `%` Wildcard

Using `_` Wildcard

Combining `%` and `_` Wildcards