MySQL RLIKE operator

RLIKE operator

MySQL RLIKE operator performs a pattern match of a string expression against a pattern. The pattern is supplied as an argument.

This function is useful in -

• Pattern matching: A regular expression allows us to find strings that match a specific pattern.
• Complex pattern matching: Regular expressions allow you to define complex patterns that are difficult to express with simple wildcards like percent signs (%) and underscores (_).

Syntax:

RLIKE pat

Argument:

The operator is the synonym for REGEXP.

MySQL RLIKE operator: Basic usage

The following MySQL statement returns all rows from the author table where the country column contains either 'USA' or 'Australia'.

Code:

SELECT * FROM author WHERE country RLIKE 'USA|Australia';

Sample table: author

Output:

aut_id|aut_name            |country  |home_city|
------+--------------------+---------+---------+
AUT006|Thomas Merton       |USA      |New York |
AUT008|Nikolai Dewey       |USA      |Atlanta  |
AUT010|Joseph Milton       |USA      |Houston  |
AUT011|John Betjeman Hunter|Australia|Sydney   |
AUT013|E. Howard           |Australia|Adelaide |
AUT015|Butler Andre        |USA      |Florida  |

Example: MySQL RLIKE operator

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

Code:

SELECT * 
FROM author 
WHERE aut_name RLIKE '^w';

Sample table: author

Output:

mysql> SELECT * FROM author 
    -> WHERE aut_name RLIKE '^w';
+--------+-----------------+---------+-----------+
| aut_id | aut_name        | country | home_city |
+--------+-----------------+---------+-----------+
| AUT001 | William Norton  | UK      | Cambridge | 
| AUT002 | William Maugham | Canada  | Toronto   | 
| AUT003 | William Anthony | UK      | Leeds     | 
+--------+-----------------+---------+-----------+
3 rows in set (0.00 sec)

Using RLIKE with character classes

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

Code:

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

Output:

Video Presentation:

All String Functions (Slides presentation)

Previous: RIGHT
Next: NOT RLIKE