Like and Not Like Operators in SQL

SQL

In this article, we will explore “Like and Not Like Operators in SQL” with various examples. let’s get started.

Like and Not Like Operators in SQL

Purpose of LIKE and NOT LIKE operators in SQL

The LIKE and NOT LIKE operators in SQL are used for pattern matching in text-based data. They allow you to search for rows in a database table where a specific column’s values match or do not match a given pattern. The primary purposes of these operators are as follows:

  1. Pattern Matching: The main purpose of the LIKE and NOT LIKE operators is to search for rows that contain or do not contain specific patterns of characters within a text column. This pattern can include literal characters and wildcard characters to represent variable portions of the text.
  2. Text Search: These operators are commonly used for text search operations within a database. You can use them to find rows that contain specific words, phrases, or substrings within text data.
  3. Data Filtering: By using LIKE and NOT LIKE, you can filter data based on specific criteria within a text column. This is particularly useful when you want to narrow down your result set to rows that meet certain textual conditions.
  4. Flexibility: The wildcard characters, % and _, provide flexibility in defining patterns. % can match any sequence of characters (including none), while _ matches any single character. This flexibility allows for a wide range of pattern-matching scenarios.
  5. Data Validation: These operators are useful for data validation, ensuring that the data stored in a column conforms to specific formats or contains or excludes certain characters or substrings.
  6. Case Insensitivity: SQL allows you to specify whether pattern matching should be case-sensitive or case-insensitive. This flexibility is essential when working with text data that may have varying cases.
  7. Complex Queries: LIKE and NOT LIKE can be used in complex SQL queries, combined with other operators and clauses like AND, OR, and NOT, to create intricate conditions for data retrieval.
  8. Negation: NOT LIKE is particularly helpful when you want to find rows that do not match a specific pattern. It provides a way to exclude rows from the result set based on pattern criteria.
  9. Regular Expressions: While SQL LIKE and NOT LIKE are not as powerful as regular expressions (regex), they offer basic pattern matching capabilities without the need for more complex regex patterns and functions.

Syntax of the LIKE operator in SQL

The LIKE operator in SQL is used to search for a specified pattern within a text column. The syntax of the LIKE operator is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;
  • LIKE: This is the SQL keyword that indicates you’re using the LIKE operator for pattern matching.
  • pattern: This is the pattern you want to search for within the specified column. The pattern can include wildcard characters that represent variable portions of the text.

Using wildcards with LIKE Pattern

Wildcards in the pattern can be used to create more flexible and dynamic search conditions:

  • %: The percent symbol represents zero, one, or multiple characters. For example, %apple would match “apple,” “pineapple,” “crabapple,” and so on.
  • _: The underscore represents a single character. For example, c_t would match “cat,” “cot,” and “cut.”

Here are a few examples of how to use the LIKE operator:

Matching patterns at the beginning of a String

Suppose you have a table named “products,” and you want to find all rows where the “product_name” column starts with the word “apple.” You can use the following SQL query:

SELECT *
FROM products
WHERE product_name LIKE 'apple%';

In this query:

  • product_name LIKE 'apple%' specifies that you want to match rows where the “product_name” starts with “apple.”

This query will retrieve all rows where the “product_name” column begins with “apple,” such as “apple pie,” “apple juice,” and so on.

Matching patterns at the end of a String

To match patterns at the end of a string in SQL using the LIKE operator, you can use the % wildcard character at the beginning of the pattern. Here’s how you can do it:

Suppose you have a table named “products,” and you want to find all rows where the “product_name” column ends with the word “apple.” You can use the following SQL query:

SELECT *
FROM products
WHERE product_name LIKE '%apple';

In this query:

  • product_name LIKE '%apple' specifies that you want to match rows where the “product_name” ends with “apple.”

This query will retrieve all rows where the “product_name” column ends with “apple,” such as “green apple,” “red apple,” and so on.

Matching patterns anywhere in a String

To match patterns anywhere in a string in SQL using the LIKE operator, you can use the % wildcard character at both the beginning and the end of the pattern. Here’s how you can do it:

Suppose you have a table named “products,” and you want to find all rows where the “product_description” column contains the word “organic” anywhere in the text. You can use the following SQL query:

SELECT *
FROM products
WHERE product_description LIKE '%organic%';

In this query:

  • product_description LIKE '%organic%' specifies that you want to match rows where the “product_description” contains the word “organic” anywhere in the text.

The % wildcard character is used at both the beginning and the end of the pattern “organic” to indicate that “organic” can appear anywhere within the “product_description” column.

This query will retrieve all rows where the “product_description” column contains the word “organic” regardless of its position in the text.

Using the ESCAPE clause with LIKE

The ESCAPE clause in SQL is used in conjunction with the LIKE operator to specify an escape character when searching for patterns. This is useful when you want to search for a pattern that includes wildcard characters like % and _ as literal characters and not as wildcards.

Suppose you have a table named “products” with the following structure:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50)
);

INSERT INTO products (product_id, product_name) VALUES
(1, 'Super% Widget'),
(2, 'Deluxe% Gadget'),
(3, 'Premium% Gizmo'),
(4, 'Basic Widget'),
(5, 'Standard Gadget');

Now, let’s say you want to search for products with names containing the “%” character as a literal character, not as a wildcard. You can use the ESCAPE clause like this:

-- Find products with names containing the literal "%" character
SELECT * FROM products WHERE product_name LIKE '%\%%';

+------------+-------------------+
| product_id |   product_name   |
+------------+-------------------+
|     1      |   Super% Widget  |
|     2      |   Deluxe% Gadget |
|     3      |   Premium% Gizmo |
+------------+-------------------+

Syntax of the NOT LIKE operator in SQL

The NOT LIKE operator in SQL is used to filter rows where a specified column does not match a particular pattern. The basic syntax of the NOT LIKE operator is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name NOT LIKE pattern;

WHERE column_name NOT LIKE pattern: Define the condition for filtering rows. The column_name represents the column you want to search, and pattern is the pattern you want to exclude from the results.

The pattern can contain wildcard characters to represent variable parts of the data:

  • %: Matches any sequence of characters (including zero characters).
  • _: Matches any single character.

For example, if you want to retrieve all rows from a “customers” table where the “email” column does not contain the word “example.com,” you can use the following query:

SELECT *
FROM customers
WHERE email NOT LIKE '%example.com';

This query will return all rows where the “email” column does not end with “example.com.” The NOT LIKE operator negates the pattern match, so it retrieves rows that don’t match the specified pattern.

Not Like in SQL Examples

When using the NOT LIKE operator in SQL, you can exclude rows that match patterns at the beginning, end, or anywhere in a string by strategically placing wildcard characters (%) in your pattern.

Matching patterns at the beginning of a string with NOT LIKE:To exclude rows where a column does not start with a particular pattern, you can use the % wildcard at the end of your pattern. For example, if you want to retrieve rows where the “product_name” column does not start with “Apple,” you can use the following query:

SELECT * FROM products WHERE product_name NOT LIKE 'Apple%';

This query will return rows where the “product_name” does not begin with “Apple.”

Matching patterns at the end of a string with NOT LIKE:To exclude rows where a column does not end with a particular pattern, you can use the % wildcard at the beginning of your pattern. For example, if you want to retrieve rows where the “file_name” column does not end with “.pdf,” you can use the following query:

SELECT * FROM files WHERE file_name NOT LIKE '%.pdf';

This query will return rows where the “file_name” does not end with “.pdf.”

Matching patterns anywhere in a string with NOT LIKE:To exclude rows where a column does not contain a particular pattern anywhere in the string, you can use the % wildcard at both the beginning and end of your pattern. For example, if you want to retrieve rows where the “description” column does not contain the word “important” anywhere, you can use the following query:

SELECT * FROM documents WHERE description NOT LIKE '%important%';

This query will return rows where the “description” does not contain the word “important” anywhere in the text.

Best Practices for Using Like and Not Like Operators in SQL

Using the LIKE and NOT LIKE operators in SQL for pattern matching is a powerful feature, but it should be used judiciously to ensure efficient query performance and accurate results. Here are some best practices for using these operators:

1. Use Indexes: When performing pattern matching on large datasets, consider indexing the columns that you intend to use with the LIKE or NOT LIKE operators. Indexing can significantly improve query performance by allowing the database to quickly locate relevant rows.

2. Be Specific in Patterns: Try to be as specific as possible in your patterns. Using wildcard characters like % and _ at the beginning of a pattern can make the query less efficient, as it may need to scan more rows. For example, if you only need to match words that start with “app,” use 'app%' instead of '%app%'.

3. Avoid Leading Wildcards: Avoid starting patterns with % if possible. Starting with a wildcard can force the database to perform a full table scan, which is less efficient. It’s often better to structure your patterns so that they can use an index.

4. Test Performance: Regularly test the performance of your LIKE and NOT LIKE queries, especially on large datasets. Database profiling tools can help identify bottlenecks and areas for optimization.

5. Parameterized Queries: When building SQL queries dynamically in applications, use parameterized queries or prepared statements to prevent SQL injection vulnerabilities.

6. Limit Result Sets: Use additional filtering criteria in combination with LIKE or NOT LIKE to limit the result set size. Avoid retrieving unnecessary data.

7. Be Aware of Pattern Escaping: If your patterns include characters that have special meanings in LIKE, such as % or _, be sure to escape or handle them correctly to prevent unintended matches. Some database systems provide functions for pattern escaping.

8. Use NOT LIKE Sparingly: While NOT LIKE is useful for excluding specific patterns, it can be computationally expensive, especially with leading wildcards. Try to structure your queries to use LIKE with positive conditions whenever possible.

Conclusion: Like and Not Like Operators in SQL

In this article, we explored the “Like and Not Like Operators in SQL” with various examples. Here’s a recap of the main points covered:

  1. Purpose of LIKE and NOT LIKE Operators in SQL:
    • These operators are used for pattern matching in text-based data.
    • They are used to search for rows in a database table where a specific column’s values match or do not match a given pattern.
    • Common purposes include pattern matching, text search, data filtering, flexibility in defining patterns, data validation, case sensitivity, handling complex queries, negation, and providing basic pattern matching without regular expressions.
  2. Syntax of the LIKE Operator in SQL:
    • The LIKE operator is used to search for a specified pattern within a text column.
    • Syntax: SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;
    • Wildcard characters % (matches any sequence of characters) and _ (matches any single character) are used to create flexible patterns.
  3. Matching Patterns:
    • Examples demonstrated how to match patterns at the beginning, end, or anywhere in a string using LIKE.
  4. Using the ESCAPE Clause with LIKE:
    • The ESCAPE clause is used with LIKE to specify an escape character when searching for patterns that include wildcard characters as literals.
  5. Syntax of the NOT LIKE Operator in SQL:
    • The NOT LIKE operator filters rows where a specified column does not match a particular pattern.
    • Syntax: SELECT column1, column2, ... FROM table_name WHERE column_name NOT LIKE pattern;
  6. Not Like in SQL Examples:
    • Examples demonstrated how to exclude rows matching patterns at the beginning, end, or anywhere in a string using NOT LIKE.
  7. Best Practices for Using Like and Not Like Operators in SQL:
    • Use indexes for efficient pattern matching on large datasets.
    • Be specific in patterns to improve query efficiency.
    • Avoid leading wildcards to prevent full table scans.
    • Test query performance regularly.
    • Use parameterized queries to prevent SQL injection.
    • Limit result sets by combining with additional filtering criteria.
    • Be aware of pattern escaping for special characters.
    • Use NOT LIKE sparingly and structure queries for efficiency.

By following these best practices, SQL developers can use LIKE and NOT LIKE operators effectively and efficiently in their queries while ensuring data accuracy and performance.

Related Articles : 

Related posts:

  1. Full Join in SQL: Comprehensive Guide
  2. Constraints in SQL
  3. Foreign Key in SQL
  4. Select Distinct Statement in SQL

Leave a Reply

Your email address will not be published. Required fields are marked *