Advanced Use Cases and Complex Queries with the SQL LIKE Operator
Tags: sql, webdev, database, search
Published: 2024-05-31 22:21:07

#Introduction

This is a sequel (Advanced) to my earlier article on how to use SQL LIKE operator. 

[You can check out the previous article by clicking this link, to understand this article better.](https://dev.to/kellyblaire/how-to-use-the-like-operator-in-sql-4kem)

The `LIKE` operator in SQL is a versatile tool for pattern matching in string columns. It is useful in a variety of scenarios, such as data validation, searching for substrings, and performing fuzzy searches. This section will cover advanced use cases for the `LIKE` operator and present complex queries to illustrate its power and flexibility.

## Advanced Use Cases for the `LIKE` Operator

1. **Data Validation and Cleaning:**
   - Identify records with improperly formatted data.
   - Validate email addresses, phone numbers, and other patterns.
   
2. **Search and Filtering:**
   - Perform case-insensitive searches.
   - Filter records based on partial matches.
   
3. **Fuzzy Matching:**
   - Handle misspellings or variations in data entries.
   - Find records with similar but not identical values.

4. **Wildcard Searches:**
   - Search for patterns at specific positions within strings.
   - Use multiple wildcards to match complex patterns.

## Complex Queries Using `LIKE` Operator

### 1. Find Email Addresses in a Specific Domain

```sql
SELECT * FROM employees WHERE email LIKE '%@example.com';
-- Expected output: id | name   | email              | department | salary | country
--                  ---------------------------------------------------------------
--                  1  | John   | john@example.com   | Sales      | 30000  | Nigeria
--                  2  | Alice  | alice@example.com  | HR         | 50000  | Ghana
--                  3  | Carol  | carol@example.com  | Marketing  | 70000  | Togo
```

### 2. Identify Records with Potentially Misspelled Names

```sql
SELECT * FROM employees WHERE name LIKE '%j%hn%' OR name LIKE '%J%hn%';
-- Expected output: id | name    | department | salary | country
--                  --------------------------------------------
--                  1  | John    | Sales      | 30000  | Nigeria
--                  7  | Johnson | IT         | 80000  | Togo
--                  10 | Aohn    | IT         | 45000  | Nigeria
--                  15 | Jody    | IT         | 47000  | Nigeria
```

### 3. Validate Phone Numbers with Specific Formats

Assume phone numbers should be in the format `(XXX) XXX-XXXX`.

```sql
SELECT * FROM contacts WHERE phone LIKE '(___) ___-____';
-- Expected output: id | name   | phone         | email
--                  --------------------------------------
--                  1  | John   | (123) 456-7890| john@example.com
--                  2  | Alice  | (987) 654-3210| alice@example.com
```

### 4. Case-Insensitive Search for Names Containing 'an'

For case-insensitive searches, you may need to use database-specific functions, such as `UPPER` or `LOWER` in some SQL dialects.

```sql
SELECT * FROM employees WHERE LOWER(name) LIKE '%an%';
-- Expected output: id | name    | department | salary | country
--                  --------------------------------------------
--                  2  | Alice   | HR         | 50000  | Ghana
--                  9  | Jane    | Marketing  | 60000  | Togo
--                  11 | Mary    | Sales      | 40000  | Ghana
--                  13 | Maria   | IT         | 62000  | Togo
--                  14 | Ryan    | IT         | 55000  | Cameroun
```

### 5. Find Names with a Specific Pattern (e.g., Names Starting and Ending with a Vowel)

```sql
SELECT * FROM employees WHERE name LIKE '[AEIOUaeiou]%[AEIOUaeiou]';
-- Expected output: id | name   | department | salary | country
--                  -------------------------------------------
--                  2  | Alice  | HR         | 50000  | Ghana
--                  5  | Emma   | HR         | 55000  | Ghana
```

### 6. Complex Pattern Matching with Multiple Wildcards

Find names that have 'a' as the second character and 'l' somewhere after it.

```sql
SELECT * FROM employees WHERE name LIKE '_a%l%';
-- Expected output: id | name   | department | salary | country
--                  -------------------------------------------
--                  2  | Alice  | HR         | 50000  | Ghana
--                  3  | Carol  | Marketing  | 70000  | Togo
--                  6  | Emma   | HR         | 55000  | Ghana
```

### 7. Identify Records Based on Complex Name Patterns

Find names where the third character is a vowel and the name is at least 5 characters long.

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

### 8. Search for Records with Specific Character Patterns

Find names that contain exactly five characters, with the middle character being 'a'.

```sql
SELECT * FROM employees WHERE name LIKE '__a__';
-- Expected output: id | name   | department | salary | country
--                  -------------------------------------------
--                  11 | Mary   | Sales      | 40000  | Ghana
--                  13 | Maria  | IT         | 62000  | Togo
```

### 9. Search for Names with Repeating Characters

Find names that have repeating characters.

```sql
SELECT * FROM employees WHERE name LIKE '%aa%' OR name LIKE '%ee%' OR name LIKE '%oo%' OR name LIKE '%ii%' OR name LIKE '%uu%';
-- Expected output: id | name   | department | salary | country
--                  -------------------------------------------
--                  5  | Emma   | HR         | 55000  | Ghana
```

## Conclusion

The `LIKE` operator, with its `%` and `_` wildcards, is a versatile and powerful tool in SQL for performing pattern-based searches. By understanding and leveraging these wildcards, you can create complex queries that cater to a wide range of practical applications, from data validation and cleaning to advanced search functionalities. Mastering the `LIKE` operator allows for more precise and efficient data retrieval, making it an essential skill for anyone working with SQL databases.


**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).