MySQL Not Like Operator

The MySQL NOT LIKE operator is used in SQL queries to filter rows that do not match a specified pattern in a column. It is the opposite of the LIKE operator, which is used to filter rows that match a pattern. The NOT LIKE operator is typically used with the WHERE clause in a SELECT statement.

The basic syntax of the NOT LIKE operator is as follows:

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

• column1, column2, ...: The columns you want to retrieve in the query.
• table: The name of the table you are querying.
• column_name: The name of the column you want to filter based on.
• pattern: The pattern to match against the column’s values.

The pattern can include wildcard characters, such as % (matches any sequence of characters) and _ (matches any single character). For example, you can use the NOT LIKE operator to find all rows in a table where a specific column does not contain a certain substring.

Here’s an example:

Suppose you have a table named products with a column named product_name, and you want to find all products that do not contain the word “apple” in their names:

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

In this query, the NOT LIKE operator is used to filter out rows where the product_name column contains the substring “apple.”

Keep in mind that the NOT LIKE operator is case-sensitive by default. If you want to perform a case-insensitive search, you can use the COLLATE clause to specify a case-insensitive collation for the column in the WHERE clause. For example:

SELECT product_name
FROM products
WHERE product_name COLLATE utf8_general_ci NOT LIKE '%apple%';

In this example, the utf8_general_ci collation is used for a case-insensitive search.