Guide: How to Say “Not Like” in SQL

When working with SQL, it is essential to know how to perform queries that involve filtering data based on certain conditions. One commonly used condition is the “not like” operator, which allows you to find records that do not match a specific pattern or value. In this comprehensive guide, we will explore various ways to express “not like” in SQL, considering both formal and informal syntax. So, let’s dive in and discover the different approaches.

Using the “NOT LIKE” Operator

The most common and formal way to express “not like” in SQL is by using the “NOT LIKE” operator. This operator allows you to find records that do not match a specified pattern. Its syntax is as follows:

SELECT column_name FROM table_name WHERE column_name NOT LIKE pattern;

Here, replace column_name with the name of the column you want to filter, table_name with the name of the table, and pattern with the specific pattern you want to exclude. Let’s see an example to illustrate:

SELECT name FROM customers WHERE name NOT LIKE 'J%';

This query selects all customers whose names do not start with the letter ‘J’.

Using Wildcards

SQL provides two commonly used wildcards, namely % (percentage sign) and _ (underscore). These wildcards can be used with the “NOT LIKE” operator to search for patterns that do not match a specific character(s) or sequence. Here are some examples:

Using % Wildcard

The % wildcard is used to match any number of characters (including zero characters). So, if you want to filter out records that do not contain a certain substring, you can utilize this wildcard as shown:

SELECT column_name FROM table_name WHERE column_name NOT LIKE '%substring%';

For instance:

SELECT title FROM books WHERE title NOT LIKE '%SQL%';

This query selects all books whose titles do not contain the word ‘SQL’ anywhere in the title.

Using _ Wildcard

The _ wildcard is used to match any single character. If you need to exclude specific patterns where you know the length of the characters, you can use this wildcard. Here’s an example:

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

Consider the following query:

SELECT email FROM users WHERE email NOT LIKE '%@_%';

This query selects all user emails that do not contain a single character between the ‘@’ symbol and the domain.

Using Regular Expressions

If you are working with a database system that supports regular expressions (regex), you can leverage their power to achieve more complex and flexible matching criteria. Below is an example of how to use regular expressions with the “NOT LIKE” operator:

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

For instance:

SELECT title FROM articles WHERE title NOT REGEXP '[0-9]';

This query selects all articles whose titles do not contain any digit.

Additional Tips

When using the “NOT LIKE” operator, keep the following tips in mind:

• Remember to enclose strings within single quotes (‘ ‘) or double quotes (” “) when using LIKE or NOT LIKE operators.
• Be aware of case sensitivity when comparing strings. Some database systems might treat comparisons as case-sensitive by default.
• Combine “NOT LIKE” with other conditions using logical operators such as AND or OR to create more complex queries.
• Always test your queries before implementing them in a production environment to ensure they return the desired results.

TIP: If you are unsure about the syntax or behavior of the “NOT LIKE” operator specific to your database system, consult the official documentation or forums for detailed insights and examples.

Conclusion

In conclusion, filtering data in SQL using the “not like” condition is crucial for performing database queries effectively. By employing the “NOT LIKE” operator along with wildcards or regular expressions, you can construct powerful queries tailored to your specific criteria. Remember to thoroughly understand your database system’s syntax and rules to ensure accurate results. With the knowledge and examples provided in this guide, you should now be well-equipped to use the “not like” condition in SQL queries successfully.