w3resource

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:

Name Description
pat A pattern which is to be matched against an expression.

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:

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

Video Presentation:

All String Functions (Slides presentation)

Previous: RIGHT
Next: NOT RLIKE



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/mysql/string-functions/mysql-rlike-function.php