How the NOT REGEXP operator works in Mariadb?

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

Syntax

The syntax of the NOT REGEXP operator is as follows:

string_value NOT REGEXP pattern

• string_value is the expression or column to be tested.
• pattern is the expression or column that defines the regular expression to be matched. It can contain any characters or symbols that are valid in regular expressions, such as . (any character), * (zero or more occurrences), + (one or more occurrences), ? (zero or one occurrence), [] (character class), () (grouping), | (alternation), etc.

Examples

Example 1: Using NOT REGEXP with the . symbol

The following example uses the NOT REGEXP operator to filter the rows from the products table where the name does not contain the character ..

SELECT id, name, price
FROM products
WHERE name NOT REGEXP '\.';

The output is:

+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  1 | Laptop    |  1000 |
|  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 . symbol is a special character in regular expressions that matches any character. Therefore, to match it literally, we need to escape it with a backslash \.

Example 2: Using NOT REGEXP with the * symbol

The following example uses the NOT REGEXP operator to filter the rows from the customers table where the email does not end with zero or more as followed by .com.

SELECT id, first_name, last_name, email
FROM customers
WHERE email NOT REGEXP 'a*\.com$';

The output is:

+----+------------+-----------+-----------------+
| id | first_name | last_name | email           |
+----+------------+-----------+-----------------+
|  6 | Harry      | Potter    | [email protected] |
+----+------------+-----------+-----------------+

Note that the * symbol is a special character in regular expressions that matches zero or more occurrences of the preceding character. The $ symbol matches the end of the string. Therefore, the pattern a*\.com$ matches any string that ends with zero or more as followed by .com.

Example 3: Using NOT REGEXP with the + symbol

The following example uses the NOT REGEXP operator to filter the rows from the orders table where the total_amount does not have two or more consecutive digits.

SELECT id, customer_id, order_date, total_amount
FROM orders
WHERE total_amount NOT REGEXP '[0-9]{2,}';

The output is:

+----+-------------+------------+---------------+
| id | customer_id | order_date | total_amount  |
+----+-------------+------------+---------------+
|  2 |           1 | 2024-01-10 |        200.00 |
|  3 |           2 | 2024-01-20 |        300.00 |
|  5 |           3 | 2024-02-15 |        300.00 |
|  6 |           4 | 2024-02-20 |        400.00 |
+----+-------------+------------+---------------+

Note that the + symbol is a special character in regular expressions that matches one or more occurrences of the preceding character. The [] symbol defines a character class that matches any character within the brackets. The {2,} symbol specifies the minimum number of repetitions of the preceding character. Therefore, the pattern [0-9]{2,} matches any string that contains two or more consecutive digits.

Example 4: Using NOT REGEXP with the ? symbol

The following example uses the NOT REGEXP operator to filter the rows from the employees table where the name does not have an optional e at the end.

SELECT id, name, department, salary
FROM employees
WHERE name NOT REGEXP 'e?$';

The output is:

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

Note that the ? symbol is a special character in regular expressions that matches zero or one occurrence of the preceding character. The $ symbol matches the end of the string. Therefore, the pattern e?$ matches any string that ends with an optional e.

Example 5: Using NOT REGEXP with the | symbol

The following example uses the NOT REGEXP operator with the | symbol to filter the rows from the products table where the name does not contain either Lap or top.

SELECT id, name, price
FROM products
WHERE name NOT REGEXP 'Lap|top';

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 | symbol is a special character in regular expressions that matches either the left or the right operand. Therefore, the pattern Lap|top matches any string that contains either Lap or top.

Related Functions

Some of the functions that are related to the NOT REGEXP operator are:

• REGEXP: This is the opposite of the NOT REGEXP operator. It 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.
• 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 REGEXP operator to achieve the same effect as the NOT REGEXP operator.
• LIKE: This is a logical operator that tests whether a string value matches a specified pattern. It returns 1 (true) if the string value matches the pattern, and 0 (false) otherwise. It can be used to test for simple patterns that contain the wildcard characters % (any sequence of characters) and _ (any single character).

For example, the following query uses the LIKE operator to filter the rows from the products table where the name contains the character o.

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

The output is:

+----+----------+-------+
| id | name     | price |
+----+----------+-------+
|  1 | Laptop   |  1000 |
|  2 | Mouse    |     5 |
|  6 | Keyboard |    25 |
|  7 | Monitor  |   200 |
|  8 | Printer  |   150 |
+----+----------+-------+

Conclusion

The NOT REGEXP operator is a useful way to filter the data based on a regular expression. 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 REGEXP operator.