MySQL NOT REGEXP operator

NOT REGEXP operator

MySQL NOT REGEXP is used to perform a pattern match of a string expression expr against a pattern pat. The pattern can be an extended regular expression.

This function is useful in -

• Negative pattern matching: It allows you to search for rows where a specific regular expression pattern does not match.
• Negation of REGEXP: The NOT REGEXP operator is the negation of the REGEXP operator. While the REGEXP operator returns rows that match a regular expression pattern, the NOT REGEXP operator returns rows that do not match the pattern.
• Complex filtering: NOT REGEXP can be combined with other comparison operators, such as AND or OR, to create complex filtering conditions.

Syntax:

expr NOT REGEXP pat

Argument

The function returns 1 if expr matches pat; otherwise, it returns 0. If either expr or pat is NULL, the result is NULL

MySQL Version: 8.0

Example: MySQL NOT REGEXP operator

The above MySQL statement will find the name of the country not beginning with ‘U’. The ‘^’ have been used to match the beginning of the name.

Code:

SELECT * FROM author 
WHERE country NOT REGEXP '^U';

Sample table: author

Output:

mysql> SELECT * FROM author 
    -> WHERE country NOT REGEXP '^U';
+--------+----------------------+-----------+----------------+
| aut_id | aut_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| AUT002 | William Maugham      | Canada    | Toronto        | 
| AUT004 | S.B.Swaminathan      | India     | Bangalore      | 
| AUT005 | Thomas Morgan        | Germany   | Arnsberg       | 
| AUT009 | Marquis de Ellis     | Brazil    | Rio De Janerio | 
| AUT011 | John Betjeman Hunter | Australia | Sydney         | 
| AUT012 | Evan Hayek           | Canada    | Vancouver      | 
| AUT013 | E. Howard            | Australia | Adelaide       | 
+--------+----------------------+-----------+----------------+
7 rows in set (0.17 sec)

Using NOT REGEXP with character classes

The following MySQL statement returns True or 1 from the given expression that does not contain three consecutive digits.

Code:

SELECT 'does12not34contain3consicutive56digits' NOT REGEXP '[0-9]{3}';

Output:

'does12not34contain3consicutive56digits' NOT REGEXP '[0-9]{3}'|
--------------------------------------------------------------+
                                                             1|

The following MySQL statement returns False or 0 from the given expression that does not contain three consecutive digits.

Code:

SELECT 'does12not345contain3consicutive67digits' NOT REGEXP '[0-9]{3}';

Output:

'does12not345contain3consicutive67digits' NOT REGEXP '[0-9]{3}'|
---------------------------------------------------------------+
                                                              0|

Example: MySQL NOT REGEXP operator

The following MySQL statement will find the author’s name not ending with ‘on’ and not ending with ‘an’. The ‘$’ character have been used to match the ending of the name.

Code:

SELECT * FROM author 
WHERE aut_name NOT REGEXP "on$" 
AND aut_name NOT REGEXP "an$";

Sample table: author

Output:

 mysql> SELECT * FROM author 
    -> WHERE aut_name NOT REGEXP "on$" 
    -> AND aut_name NOT REGEXP "an$";
+--------+----------------------+-----------+----------------+
| aut_id | aut_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| AUT002 | William Maugham      | Canada    | Toronto        | 
| AUT003 | William Anthony      | UK        | Leeds          | 
| AUT008 | Nikolai Dewey        | USA       | Atlanta        | 
| AUT009 | Marquis de Ellis     | Brazil    | Rio De Janerio | 
| AUT011 | John Betjeman Hunter | Australia | Sydney         | 
| AUT012 | Evan Hayek           | Canada    | Vancouver      | 
| AUT013 | E. Howard            | Australia | Adelaide       | 
| AUT014 | C. J. Wilde          | UK        | London         | 
| AUT015 | Butler Andre         | USA       | Florida        | 
+--------+----------------------+-----------+----------------+
9 rows in set (0.01 sec)

Example: MySQL NOT REGEXP operator basic usage

The following MySQL statement returns all rows from the author table where the aut_name does not contain the words 'William' or 'Hunter'.

Code:

SELECT * FROM author WHERE aut_name NOT REGEXP 'William|Hunter';

Sample table: author

Output:

aut_id|aut_name        |country  |home_city     |
------+----------------+---------+--------------+
AUT004|S.B.Swaminathan |India    |Bangalore     |
AUT005|Thomas Morgan   |Germany  |Arnsberg      |
AUT006|Thomas Merton   |USA      |New York      |
AUT007|Piers Gibson    |UK       |London        |
AUT008|Nikolai Dewey   |USA      |Atlanta       |
AUT009|Marquis de Ellis|Brazil   |Rio De Janerio|
AUT010|Joseph Milton   |USA      |Houston       |
AUT012|Evan Hayek      |Canada   |Vancouver     |
AUT013|E. Howard       |Australia|Adelaide      |
AUT014|C. J. Wilde     |UK       |London        |
AUT015|Butler Andre    |USA      |Florida       |

Video Presentation:

All String Functions (Slides presentation)

Previous: NOT LIKE
Next: OCTET_LENGTH