How the NOT-LIKE operator works in Mariadb?

The NOT-LIKE operator is a logical operator that tests whether a string value does not match a specified pattern.

Posted on

The NOT-LIKE operator is a logical operator that tests whether a string value does not match a specified pattern. It is equivalent to using the NOT operator with the LIKE operator. The NOT-LIKE operator returns 1 (true) if the string value does not match the pattern, and 0 (false) otherwise.

Syntax

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

string_value NOT LIKE pattern [ESCAPE escape_character]
  • string_value is the expression or column to be tested.
  • pattern is the expression or column that defines the pattern to be matched. It can contain the following wildcard characters:
    • % matches any sequence of zero or more characters.
    • _ matches any single character.
  • ESCAPE is an optional clause that specifies the escape character to be used in the pattern. The escape character can be used to treat the wildcard characters as literal characters. The default escape character is \.

Examples

Example 1: Using NOT-LIKE with the % wildcard

The following example uses the NOT-LIKE operator to filter the rows from the products table where the name does not start with ‘P’.

SELECT id, name, price
FROM products
WHERE name NOT LIKE 'P%';

The output is:

+----+----------+-------+
| id | name     | price |
+----+----------+-------+
|  1 | Laptop   |  1000 |
|  2 | Mouse    |     5 |
|  3 | Pen      |    10 |
|  6 | Keyboard |    25 |
|  7 | Monitor  |   200 |
+----+----------+-------+

Example 2: Using NOT-LIKE with the _ wildcard

The following example uses the NOT-LIKE operator to filter the rows from the customers table where the email does not have exactly four characters before the @ symbol.

SELECT id, first_name, last_name, email
FROM customers
WHERE email NOT LIKE '____@%';

The output is:

+----+------------+-----------+---------------------+
| id | first_name | last_name | email               |
+----+------------+-----------+---------------------+
|  1 | David      | Lee       | [email protected]   |
|  2 | Alice      | Smith     | [email protected] |
|  3 | Bob        | Jones     | [email protected]   |
|  5 | Emma       | Watson    | [email protected] |
|  6 | Harry      | Potter    | [email protected]     |
+----+------------+-----------+---------------------+

Example 3: Using NOT-LIKE with the ESCAPE clause

The following example uses the NOT-LIKE operator with the ESCAPE clause to filter the rows from the employees table where the name does not contain the character _.

SELECT id, name, department, salary
FROM employees
WHERE name NOT LIKE '%\_%' ESCAPE '\';

The output is:

+----+-------+------------+--------+
| id | name  | department | salary |
+----+-------+------------+--------+
|  1 | John  | Sales      |   2000 |
|  2 | Jane  | Marketing  |   6000 |
|  3 | Jack_ | Finance    |   4000 |
|  4 | Mary  | HR         |   NULL |
|  5 | Peter | IT         |   2500 |
+----+-------+------------+--------+

Note that the escape character \ is used to treat the _ character as a literal character, not a wildcard character.

Example 4: Using NOT-LIKE with NULL values

The following example uses the NOT-LIKE operator to filter the rows from the employees table where the name does not end with ’e’, or is NULL.

SELECT id, name, department, salary
FROM employees
WHERE name NOT LIKE '%e'
OR name IS NULL;

The output is:

+----+-------+------------+--------+
| id | name  | department | salary |
+----+-------+------------+--------+
|  1 | John  | Sales      |   2000 |
|  3 | Jack_ | Finance    |   4000 |
|  4 | Mary  | HR         |   NULL |
|  5 | Peter | IT         |   2500 |
|  6 | NULL  | IT         |   7000 |
+----+-------+------------+--------+

Note that the NOT-LIKE operator returns NULL if any of the operands is NULL. Therefore, to include the rows with NULL values, we need to use the OR operator with the IS NULL operator.

Example 5: Using NOT-LIKE with a subquery

The following example uses the NOT-LIKE operator with a subquery to filter the rows from the products table where the name does not match the pattern returned by the subquery.

SELECT id, name, price
FROM products
WHERE name NOT LIKE (SELECT CONCAT('%', name, '%') FROM products WHERE id = 1);

The output is:

+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  2 | Mouse     |     5 |
|  3 | Pen       |    10 |
|  4 | Notebook  |    15 |
|  5 | USB Drive |    15 |
|  6 | Keyboard  |    25 |
|  7 | Monitor   |   200 |
|  8 | Printer   |   150 |
|  9 | Scanner   |   120 |
+----+-----------+-------+

Note that the subquery returns the pattern '%Laptop%', which is used to match the name column.

Some of the functions that are related to the NOT-LIKE operator are:

  • LIKE: This is the opposite of the NOT-LIKE operator. It tests whether a string value matches a specified pattern. It returns 1 (true) if the string value matches the pattern, and 0 (false) otherwise.
  • NOT: This is a logical operator that negates the result of another operator or expression. It returns 1 (true) if the operand is 0 (false), and 0 (false) if the operand is 1 (true) or NULL. It can be used with the LIKE operator to achieve the same effect as the NOT-LIKE operator.
  • REGEXP: This is a logical operator that tests whether a string value matches a regular expression. It returns 1 (true) if the string value matches the regular expression, and 0 (false) otherwise. It can be used to test for more complex patterns than the LIKE operator.

For example, the following query uses the REGEXP operator to filter the rows from the products table where the name contains two consecutive vowels.

SELECT id, name, price
FROM products
WHERE name REGEXP '[aeiou]{2}';

The output is:

+----+----------+-------+
| id | name     | price |
+----+----------+-------+
|  1 | Laptop   |  1000 |
|  5 | USB Drive|    15 |
|  7 | Monitor  |   200 |
|  8 | Printer  |   150 |
+----+----------+-------+

Conclusion

The NOT-LIKE operator is a useful way to filter the data based on a string pattern. It can be used with any string data type, such as char, varchar, text, etc. It is equivalent to using the NOT operator with the LIKE operator. It returns 1 (true) if the string value does not match the pattern, and 0 (false) otherwise. To include the rows with NULL values, we need to use the OR operator with the IS NULL operator. Some of the related functions are LIKE, NOT, and REGEXP.