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:
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics