MySQL NOT RLIKE operator

NOT RLIKE operator

MySQL NOT RLIKE operator checks whether a pattern is not present within an expression. The pattern is supplied as an argument.

This function is useful in -

• Negative pattern matching: Searching for rows that don't match a regular expression pattern.
• Negation of RLIKE: The NOT RLIKE operator is the negation of the RLIKE operator.
• Complex filtering: NOT RLIKE can be combined with other comparison operators, such as AND or OR, to create complex filtering conditions.

Syntax:

NOT RLIKE pat

Argument:

MySQL Version: 8.0

MySQL NOT RLIKE operator: Basic usage

The following MySQL statement returns all rows from the author table where the country column contains neither 'USA' nor 'UK'.

Code:

SELECT * FROM author WHERE country NOT RLIKE 'USA|UK';

Sample table: author

Output:

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      |

Using NOT RLIKE with character classes

The following MySQL statement returns true(1) or false(0)from the expression given here, where the text contains three digits consecutively

Code:

SELECT 'three continious123exists' NOT RLIKE '[0-9]{3}';

Output:

'three continious123exists' NOT RLIKE '[0-9]{3}'|
------------------------------------------------+
                                               0|

Example: MySQL NOT RLIKE operator

The following MySQL statement will find the author’s name not beginning with ‘w’ and country not beginning with 'U'. The ‘^’ have been used to match the beginning of the name.

Code:

SELECT * FROM author 
WHERE aut_name NOT RLIKE '^w' 
AND country NOT RLIKE '^U';

Sample table: author

Output:

mysql> SELECT * FROM author 
    -> WHERE aut_name NOT RLIKE '^w' 
    -> AND country NOT RLIKE '^U';
+--------+----------------------+-----------+----------------+
| aut_id | aut_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| 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       | 
+--------+----------------------+-----------+----------------+
6 rows in set (0.00 sec)

Video Presentation:

All String Functions (Slides presentation)

Previous: RLIKE
Next: RPAD