LIKE Operator

The LIKE operator in Oracle SQL is used to search for a specified pattern within a column. It is typically used in the WHERE clause to filter records based on partial matches rather than exact values. This operator is useful for performing wildcard searches, such as when you need to find strings that match a certain format.

 

1. Basic Syntax of LIKE Operator

The basic syntax for the LIKE operator is:

SELECT column1, column2, ...

FROM table_name

WHERE column_name LIKE pattern;

• column_name: The name of the column to search for the pattern.
• pattern: The pattern you want to search for. This pattern may include wildcard characters.

 

2. Wildcard Characters Used with LIKE

The LIKE operator supports two primary wildcard characters:

• %: The percent sign represents zero, one, or multiple characters. It can be used at the beginning, middle, or end of the pattern.
• _: The underscore represents a single character. It is used to match exactly one character in a string.

Examples of wildcard usage:

• % (percent): Used to match zero or more characters.
• _ (underscore): Used to match exactly one character.

 

3. Using the % Wildcard

The % wildcard allows you to match any number of characters (including zero characters). It is typically used when you're unsure of the number of characters in a field but know part of the string.

Example:

SELECT * FROM employees

WHERE last_name LIKE 'Sm%';

This query will return all employees whose last name starts with "Sm" (e.g., "Smith", "Smythe", etc.).

Example with % at the end:

SELECT * FROM employees

WHERE last_name LIKE '%son';

This query will return all employees whose last name ends with "son" (e.g., "Johnson", "Wilson", etc.).

Example with % in the middle:

SELECT * FROM employees

WHERE last_name LIKE '%ith%';

This query will return all employees whose last name contains "ith" (e.g., "Smith", "Smythe", "Pritchard", etc.).

 

4. Using the _ (Underscore) Wildcard

The _ wildcard is used to match exactly one character in a string. It's helpful when you know the structure of a string but need to account for a single unknown character.

Example:

SELECT * FROM employees

WHERE last_name LIKE 'Sm_th';

This query will return all employees whose last name is five characters long and starts with "Sm" and ends with "th" (e.g., "Smith", "Smyth").

Example with multiple underscores:

SELECT * FROM employees

WHERE last_name LIKE '_r_wn';

This query will return employees whose last name is four characters long, where the second character is "r" and the third character is "w" (e.g., "Brown").

 

5. Case Sensitivity in LIKE

By default, the LIKE operator in Oracle is case-insensitive. However, this behavior can depend on the database's collation settings. If you need to perform case-sensitive searches, you can use the BINARY keyword in some other SQL databases, but in Oracle, you typically use the UPPER or LOWER functions to enforce case sensitivity.

Example of case-insensitive search:

SELECT * FROM employees

WHERE last_name LIKE 'smith';  -- This will match 'Smith', 'SMITH', etc.

For case-sensitive search:

SELECT * FROM employees

WHERE UPPER(last_name) LIKE 'SMITH';

This ensures that only records with the exact uppercase "SMITH" will be returned.

 

6. Using LIKE with NOT

The NOT LIKE operator is used when you want to find rows where a column does not match a specified pattern. It filters out rows that match the pattern.

Example:

SELECT * FROM employees

WHERE last_name NOT LIKE 'S%';

This query will return all employees whose last name does not start with "S".

 

7. Combining LIKE with Other Operators

You can combine the LIKE operator with other SQL conditions (e.g., AND, OR, NOT, etc.) to refine your search criteria.

Example using AND:

SELECT * FROM employees

WHERE last_name LIKE 'Sm%' AND department_id = 10;

This query will return all employees whose last name starts with "Sm" and who work in department 10.

Example using OR:

SELECT * FROM employees

WHERE last_name LIKE 'Sm%' OR department_id = 10;

This query will return all employees whose last name starts with "Sm", or who work in department 10.

 

8. Using LIKE with Numbers and Dates

You can also use LIKE with numbers and dates, but the syntax requires them to be converted to strings.

Example with numbers:

SELECT * FROM orders

WHERE order_number LIKE '10%';

This query will return all orders whose order number starts with "10".

Example with dates:

SELECT * FROM orders

WHERE TO_CHAR(order_date, 'YYYY-MM-DD') LIKE '2021-08%';

This query will return all orders from August 2021 by converting the date to a string and matching the pattern.

 

9. Performance Considerations with LIKE

• Use wildcards wisely: The % wildcard at the beginning of the pattern (LIKE '%something') can lead to slower queries, as it prevents the database from using indexes effectively. If possible, avoid using the % wildcard at the beginning.
• Indexes: While the LIKE operator can use indexes when the pattern starts with a fixed string (e.g., LIKE 'ABC%'), using % at the start will usually prevent the use of indexes, resulting in slower queries.
• Long patterns: When the pattern is long or complex, the query performance can degrade. Consider simplifying the pattern or breaking up the query into smaller parts.

 

10. Practical Examples of LIKE

• Find names that contain "john" anywhere:

SELECT * FROM employees

WHERE first_name LIKE '%john%';

• Find records that start with "A" and have any two characters after it:

SELECT * FROM employees

WHERE last_name LIKE 'A__';

• Find records where the second character is "a":

SELECT * FROM employees

WHERE last_name LIKE '_a%';

 

11. LIKE vs REGEXP_LIKE in Oracle

In some cases, you may want to perform more complex pattern matching. Oracle offers the REGEXP_LIKE function, which allows for regular expressions to be used in pattern matching, providing more advanced capabilities than the simple LIKE operator.

Example with REGEXP_LIKE:

SELECT * FROM employees

WHERE REGEXP_LIKE(last_name, '^S.*h$');

This will find all last names that start with "S" and end with "h" (e.g., "Smith", "Smyth").

 

12. Common Use Cases for LIKE

• Search for a pattern in names, emails, or other textual data.
• Performing fuzzy searches where exact matches are not required.
• Validating input (e.g., checking if a phone number or email follows a certain pattern).
• Filtering results by partial strings (e.g., matching a part of an address, title, or product name).