How to Use the 'LIKE' Operator in SQL
Tags: webdev, sql, database, search
Published: 2024-05-31 22:08:28

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:

```sql
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
```

## Wildcards Used with `LIKE`

The `LIKE` operator is often used with two wildcards:

1. `%` (Percent): Represents zero, one, or multiple characters.
2. `_` (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:**

1. **Match any string that starts with 'J':**

```sql
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
```

2. **Match any string that ends with 'n':**

```sql
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
```

3. **Match any string that contains 'ar':**

```sql
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
```

4. **Match any string that contains 'a' as the second character:**

```sql
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:**

1. **Match names that start with any character followed by 'ohn':**

```sql
SELECT * FROM employees WHERE name LIKE '_ohn';
-- Expected output: id | name  | department | salary | country
--                  -----------------------------------------
--                  1  | John  | Sales      | 30000  | Nigeria
--                  10 | Aohn  | IT         | 45000  | Nigeria
```

2. **Match names that have 'e' as the third character:**

```sql
SELECT * FROM employees WHERE name LIKE '__e%';
-- Expected output: id | name   | department | salary | country
--                  -------------------------------------------
--                  4  | Steve  | IT         | 60000  | Cameroun
--                  12 | Helen  | HR         | 65000  | Cameroun
```

3. **Match names that have exactly 5 characters, with 'a' as the second character:**

```sql
SELECT * FROM employees WHERE name LIKE '_a___';
-- Expected output: id | name   | department | salary | country
--                  -------------------------------------------
--                  3  | Carol  | Marketing  | 70000  | Togo
--                  13 | Maria  | IT         | 62000  | Togo
```

4. **Match names where the fourth character is 'n':**

```sql
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:**

1. **Match names that start with 'J' and have 'o' as the third character:**

```sql
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
```

2. **Match names that contain 'a' followed by exactly one character and then 'e':**

```sql
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

1. **Match names that start with 'J':**

```sql
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
```

2. **Match names that contain 'ar':**

```sql
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
```

3. **Match names where the fourth character is 'n':**

```sql
SELECT * FROM employees WHERE name LIKE '___n%';
-- Expected output: id | name   | department | salary | country
--                  -------------------------------------------
--                  1  | John   | Sales      | 30000  | Nigeria
--                  14 | Ryan   | IT         | 55000  | Cameroun
```

4. **Match names that start with any character followed by 'ohn':**

```sql
SELECT * FROM employees WHERE name LIKE '_ohn';
-- Expected output: id | name  | department | salary | country
--                  -----------------------------------------
--                  1  | John  | Sales      | 30000  | Nigeria
--                  10 | Aohn  | IT         | 45000  | Nigeria
```

5. **Match names that have 'e' as the third character:**

```sql
SELECT * FROM employees WHERE name LIKE '__e%';
-- Expected output: id | name   | department | salary

 | country
--                  -------------------------------------------
--                  4  | Steve  | IT         | 60000  | Cameroun
--                  12 | Helen  | HR         | 65000  | Cameroun
```

6. **Match names that have exactly 5 characters, with 'a' as the second character:**

```sql
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**.](https://dev.to/kellyblaire/advanced-use-cases-and-complex-queries-with-the-sql-like-operator-3p3k)



**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](https://buymeacoffee.com/yafskkw).