Kelly OkereIntroduction
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.
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
-
Data Validation and Cleaning:
- Identify records with improperly formatted data.
- Validate email addresses, phone numbers, and other patterns.
-
Search and Filtering:
- Perform case-insensitive searches.
- Filter records based on partial matches.
-
Fuzzy Matching:
- Handle misspellings or variations in data entries.
- Find records with similar but not identical values.
-
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
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
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.
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.
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)
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.
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.
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'.
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.
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.
