Introduction
The LIKE operator is a powerful SQL feature that allows for pattern matching in string comparisons. Unlike the equality operator (=) which requires an exact match, LIKE enables flexible searches using wildcard characters.
LIKE Operator Syntax
|
1 2 3 |
SELECT columns FROM table WHERE column_name LIKE pattern; |
Wildcard Characters
The LIKE operator uses two primary wildcard characters:
-
- % (percent): Represents zero, one, or multiple characters
- _ (underscore): Represents exactly one character
The ESCAPE Character
When you need to search for the actual % or _ characters in your data, use the ESCAPE clause:
|
1 2 3 |
SELECT columns FROM table WHERE column_name LIKE pattern ESCAPE escape_character; |
Practice Exercises Using Oracle HR Schema
Exercise 1: Basic Pattern Matching
Let's start with simple pattern matching to find employees whose last names start with ‘K':
|
1 2 3 |
SELECT employee_id, first_name, last_name FROM employees WHERE last_name LIKE 'K%'; |
Explanation: This query returns all employees whose last name begins with ‘K' followed by any number of characters (including zero).
Exercise 2: Using the Underscore Wildcard
Find all employees whose first name is exactly 5 characters long:
|
1 2 3 |
SELECT employee_id, first_name, last_name FROM employees WHERE first_name LIKE '_____'; |
Explanation: Each underscore represents exactly one character, so five underscores match any first name with exactly 5 characters.
Exercise 3: Combined Wildcards
Find employees whose last name has ‘a' as the second letter:
|
1 2 3 |
SELECT employee_id, first_name, last_name FROM employees WHERE last_name LIKE '_a%'; |
Explanation: This pattern matches any last name where:
-
- The first character can be anything (_)
- The second character must be ‘a'
- The remaining characters can be anything or nothing (%)
Exercise 4: Finding Patterns Within Strings
Find all departments with ‘ing' anywhere in their name:
|
1 2 3 |
SELECT department_id, department_name FROM departments WHERE department_name LIKE '%ing%'; |
Explanation: The ‘%ing%' pattern searches for ‘ing' anywhere in the department name.
Exercise 5: Using the ESCAPE Clause
Find any job_id that contains a ‘%' character:
|
1 2 3 |
SELECT job_id, job_title FROM jobs WHERE job_id LIKE '%\%%' ESCAPE '\'; |
Explanation: This query searches for job_ids containing the % character. The backslash (\\) is defined as the escape character, telling SQL to treat the % after it as a literal character, not a wildcard.
Exercise 6: Case Sensitivity
Oracle's LIKE operator is case-sensitive by default. Find employees with first names that start with ‘a' (lowercase):
|
1 2 3 |
SELECT employee_id, first_name, last_name FROM employees WHERE first_name LIKE 'a%'; |
Now compare with employees whose names start with ‘A' (uppercase):
|
1 2 3 |
SELECT employee_id, first_name, last_name FROM employees WHERE first_name LIKE 'A%'; |
Explanation: These are different queries that will return different results due to case sensitivity.
Exercise 7: Case Insensitive Search
To perform a case-insensitive search, use the UPPER or LOWER functions:
|
1 2 3 |
SELECT employee_id, first_name, last_name FROM employees WHERE UPPER(first_name) LIKE 'A%'; |
Exercise 8: Finding Names with Specific Endings
Find employees whose last name ends with ‘son':
|
1 2 3 |
SELECT employee_id, first_name, last_name FROM employees WHERE last_name LIKE '%son'; |
Exercise 9: Finding Job IDs with Underscore
Find all jobs with ‘REP' in their ID (note that job_ids contain underscores):
|
1 2 3 |
SELECT job_id, job_title FROM jobs WHERE job_id LIKE '%REP%'; |
Explanation: This will find any job_id with ‘REP' anywhere in it.
Challenge Exercises
Challenge 1: Finding Specific Name Patterns
Find employees who have the same letter repeated twice in their last name:
|
1 2 3 4 5 6 7 |
SELECT employee_id, last_name FROM employees WHERE last_name LIKE '%a%a%' OR last_name LIKE '%b%b%' OR last_name LIKE '%c%c%' OR /<em> ... continue for all letters ... </em>/ last_name LIKE '%z%z%'; |
Alternative solution using regular expressions (Oracle specific):
|
1 2 3 |
SELECT employee_id, last_name FROM employees WHERE REGEXP_LIKE(last_name, '([a-zA-Z]).*\1'); |
Challenge 2: Combined Conditions
Find employees who:
-
- Have a job ID starting with ‘SA'
- Have a last name containing ‘e'
- Have a salary greater than 10000
|
1 2 3 4 5 |
SELECT employee_id, first_name, last_name, job_id, salary FROM employees WHERE job_id LIKE 'SA%' AND last_name LIKE '%e%' AND salary > 10000; |
Challenge 3: Find Managers in Department Names
Find all departments with ‘Manager' in their name, and list the employees who work in those departments:
|
1 2 3 4 |
SELECT e.employee_id, e.first_name, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.department_name LIKE '%Manager%'; |
Performance Considerations
Pattern matching with LIKE, especially with leading wildcards (‘%xyz'), can result in full table scans and poor performance. For large tables, consider:
-
- Indexes: Standard B-tree indexes are ineffective for LIKE with leading wildcards
- Function-based indexes: Can help with case-insensitive searches
- Full-text indexing: For complex text searching beyond LIKE capabilities
LIKE vs. Regular Expressions
For more complex pattern matching, Oracle provides regular expression functions:
|
1 2 3 4 |
-- Find employees with last names containing exactly two 'a's SELECT employee_id, last_name FROM employees WHERE REGEXP_LIKE(last_name, '^([^a]<em>a[^a]</em>a[^a]*)$'); |
Summary
The LIKE operator is a versatile tool for pattern matching in SQL:
-
- Use % for zero or more characters
- Use _ for exactly one character
- For literal % or _ characters, use the ESCAPE clause
- Combine with other SQL conditions for powerful filtering
- Consider case sensitivity in your searches
- Be aware of performance implications with large datasets
Additional Exercises
To further practice the LIKE operator, try:
-
- Find employees with three-part job IDs (two underscores)
- Find locations with city names starting with a vowel
- Find departments whose name contains the word “Sales” or “Marketing”
- Find employees hired in a specific month across any year
- Create a case-insensitive search for job titles containing “manager” or “director”
