MySQL is an open-source relational database management system that is widely used for managing data. One of the most important features of MySQL is the ability to search and filter data using the WHERE clause. The WHERE clause allows you to filter data based on specific conditions. In this article, we will look at how to use the WHERE NOT LIKE operator to filter data.
The WHERE NOT LIKE operator is used to filter data based on a specific pattern or text that is not present in the data. For example, if you want to find all the records in a table that do not contain a specific word or phrase, you can use the WHERE NOT LIKE operator.
Syntax of WHERE NOT LIKE Operator
The WHERE NOT LIKE operator uses the following syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name NOT LIKE pattern;
The column_name(s) refer to the name of the column(s) that you want to search, and the table_name refers to the name of the table where the data is stored. The pattern is the string of characters that you want to search for.
Using the WHERE NOT LIKE Operator
To use the WHERE NOT LIKE operator, you need to specify the pattern that you want to search for. The pattern can include wildcards such as % and _.
The % wildcard represents any number of characters, and the _ wildcard represents a single character. For example, if you want to search for all the records in a table that do not contain the word “apple”, you can use the following query:
SELECT *
FROM fruits
WHERE name NOT LIKE ‘%apple%’;
This query will return all the records in the fruits table that do not contain the word “apple” in the name column.
You can also use the WHERE NOT LIKE operator with multiple conditions. For example, if you want to search for all the records in a table that do not contain the word “apple” in the name column and have a quantity greater than 10, you can use the following query:
SELECT *
FROM fruits
WHERE name NOT LIKE ‘%apple%’
AND quantity > 10;
This query will return all the records in the fruits table that do not contain the word “apple” in the name column and have a quantity greater than 10.
Using the NOT Operator
The WHERE NOT LIKE operator can also be used in conjunction with the NOT operator. The NOT operator is used to negate a condition. For example, if you want to search for all the records in a table that do not contain the word “apple” or “banana” in the name column, you can use the following query:
SELECT *
FROM fruits
WHERE NOT (name LIKE ‘%apple%’ OR name LIKE ‘%banana%’);
This query will return all the records in the fruits table that do not contain the word “apple” or “banana” in the name column.
Using Regular Expressions
MySQL also supports regular expressions, which are a powerful way to search for patterns in data. To use regular expressions with the WHERE NOT LIKE operator, you need to use the REGEXP keyword.
For example, if you want to search for all the records in a table that do not contain a number in the name column, you can use the following query:
SELECT *
FROM fruits
WHERE name NOT REGEXP ‘[0-9]’;
This query will return all the records in the fruits table that do not contain a number in the name column.
The WHERE NOT LIKE operator is a powerful tool for filtering data in MySQL. It allows you to search for patterns and text that are not present in the data. By using wildcards and regular expressions, you can create complex and precise searches that will help you find the data you need. When using the WHERE NOT LIKE operator, it is important to use it in conjunction with other operators and conditions to create the most effective search.