Oracle

The NOT LIKE operator in Oracle is used in SQL queries to filter records based on pattern matching. It allows you to select rows where a specified column's value does not match a particular pattern. This operator is especially useful when dealing with text data, enabling you to exclude unwanted records based on specific criteria.

Key Points about the NOT LIKE Operator

1. Basic Syntax:

   • The basic syntax for the NOT LIKE operator is:

     SELECT column_name
     FROM table_name
     WHERE column_name NOT LIKE 'pattern';
     

2. Wildcard Characters:

   • You can use two wildcard characters with the NOT LIKE operator:
     • %: Represents zero or more characters.
     • _: Represents a single character.
   • Example:

     SELECT *
     FROM books
     WHERE title NOT LIKE 'A%';
     

     This query retrieves all books whose titles do not start with the letter "A".

3. Example with Authors Table:

   • Here's an example using an authors table to find authors whose names do not start with "John":

     SELECT *
     FROM authors
     WHERE author_name NOT LIKE 'John%';
     

4. Case Sensitivity:

   • The NOT LIKE operator is case-sensitive in Oracle. To perform a case-insensitive search, you can use the UPPER or LOWER functions.
     
     
   • Example:

     SELECT *
     FROM members
     WHERE UPPER(member_name) NOT LIKE 'A%';
     

5. Combining Conditions:

   • You can combine the NOT LIKE operator with other conditions using AND or OR.
   • Example:

     SELECT *
     FROM rentals
     WHERE rental_status NOT LIKE 'Returned%' AND rental_date NOT LIKE '2024%';
     

Best Practices

1. Use Wildcards Wisely:

   • Avoid leading wildcards (e.g., %text) as they can lead to performance issues since they prevent the use of indexes.

2. Optimize Queries:

   • Use the NOT LIKE operator judiciously in large datasets. Consider indexing columns that are frequently queried with this operator for better performance.

3. Test Patterns:

   • Always test your patterns with sample data to ensure the expected results are returned, especially when using wildcard characters.

4. Consider Case Sensitivity:

   • Be mindful of case sensitivity in your queries and adjust accordingly using string functions when necessary.

By understanding the NOT LIKE operator, you can effectively filter data in Oracle databases, ensuring you retrieve only the relevant records for your queries.

To gain complete access, login with gmail or outlook, no need of signup. click here

SELECT *
FROM org_client
WHERE last_name NOT LIKE 'M%';

SELECT *
FROM org_client
WHERE first_name NOT LIKE '%e';

Example 1:

Explore the procedure of retrieving data from a designated table using the SQL NOT LIKE operator. Fetch records for clients whose last names do not start with 'M%', where '%' indicates the potential presence of zero or more characters following 'M'.

Example 1 - Raw data from client table

Example 1 - Query

SELECT *
FROM org_client
WHERE last_name NOT LIKE 'M%';

Example 1 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 1 - Query Output

Example 2:

Retrieve entries for clients whose first names do not conclude with '%e'.

Example 2 - Query

SELECT *
FROM org_client
WHERE first_name NOT LIKE '%e';

Example 2 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 2 - Query Output