RLIKE¶
Tests if a string matches a given regular expression pattern.
RLIKE
and LIKE are similar, but RLIKE
uses POSIX regular expressions instead of the SQL patterns.
See also: LIKE, REGEXP_COUNT, REGEXP_INSTR, REGEXP_SUBSTR.
Syntax¶
string_expr [ NOT ] RLIKE string_test_expr
Arguments¶
Parameter | Description |
---|---|
string_expr |
String to test |
string_test_expr |
Test pattern |
Test patterns¶
Pattern | Description |
---|---|
^ |
Match the beginning of a string |
$ |
Match the end of a string |
. |
Match any character (including whitespace such as carriage return and newline) |
* |
Match the preceding pattern zero or more times |
+ |
Match the preceding pattern at least once |
? |
Match the preceding pattern once at most |
de|abc |
Match either de or abc |
(abc)* |
Match zero or more instances of the sequence abc |
{2} |
Match the preceding pattern exactly two times |
{2,4} |
Match the preceding pattern between two and four times |
[a-dX] , [^a-dX] |
Matches any character that is (or is not when negated with ^ ) either a , b , c , d , or X .
The - character between two other characters forms a range that matches all characters from the first character to the second. For example, [0-9] matches any decimal digit.
To include a literal ] character, it must immediately follow the opening bracket [. To include a literal - character, it must be written first or last.
Any character that does not have a defined special meaning inside a [] pair matches only itself. |
Returns¶
TRUE
if the test string matches the pattern, or FALSE
otherwise.
Notes¶
- The test pattern must be literal string. Column references or complex expressions are currently unsupported.
- If the value is NULL, the result is NULL.
Examples¶
For these examples, assume a table named nba
, with the following structure:
CREATE TABLE nba
(
Name varchar(40),
Team varchar(40),
Number tinyint,
Position varchar(2),
Age tinyint,
Height varchar(4),
Weight real,
College varchar(40),
Salary float
);
Here’s a peek at the table contents (Download nba.csv
):
Name | Team | Number | Position | Age | Height | Weight | College | Salary |
---|---|---|---|---|---|---|---|---|
Avery Bradley | Boston Celtics | 0.0 | PG | 25.0 | 6-2 | 180.0 | Texas | 7730337.0 |
Jae Crowder | Boston Celtics | 99.0 | SF | 25.0 | 6-6 | 235.0 | Marquette | 6796117.0 |
John Holland | Boston Celtics | 30.0 | SG | 27.0 | 6-5 | 205.0 | Boston University | |
R.J. Hunter | Boston Celtics | 28.0 | SG | 22.0 | 6-5 | 185.0 | Georgia State | 1148640.0 |
Jonas Jerebko | Boston Celtics | 8.0 | PF | 29.0 | 6-10 | 231.0 | 5000000.0 | |
Amir Johnson | Boston Celtics | 90.0 | PF | 29.0 | 6-9 | 240.0 | 12000000.0 | |
Jordan Mickey | Boston Celtics | 55.0 | PF | 21.0 | 6-8 | 235.0 | LSU | 1170960.0 |
Kelly Olynyk | Boston Celtics | 41.0 | C | 25.0 | 7-0 | 238.0 | Gonzaga | 2165160.0 |
Terry Rozier | Boston Celtics | 12.0 | PG | 22.0 | 6-2 | 190.0 | Louisville | 1824360.0 |
Match the beginning of a string¶
This form is equivalent to ... LIKE "Portland%"
nba=> SELECT "Name","Age","Salary","Team" FROM nba WHERE "Team" RLIKE '^(Portland)+' LIMIT 5;
Name | Age | Salary | Team
----------------+-----+---------+-----------------------
Cliff Alexander | 20 | 525093 | Portland Trail Blazers
Al-Farouq Aminu | 25 | 8042895 | Portland Trail Blazers
Pat Connaughton | 23 | 625093 | Portland Trail Blazers
Allen Crabbe | 24 | 947276 | Portland Trail Blazers
Ed Davis | 27 | 6980802 | Portland Trail Blazers
Negate with NOT
¶
nba=> SELECT "Name","Age","Salary","Team" FROM nba WHERE "Team" NOT RLIKE '^(Portland)+' LIMIT 5;
Name | Age | Salary | Team
--------------+-----+---------+---------------
Avery Bradley | 25 | 7730337 | Boston Celtics
Jae Crowder | 25 | 6796117 | Boston Celtics
John Holland | 27 | | Boston Celtics
R.J. Hunter | 22 | 1148640 | Boston Celtics
Jonas Jerebko | 29 | 5000000 | Boston Celtics
Match the middle of a string¶
nba=> SELECT "Name","Age","Salary","Team" FROM nba WHERE "Team" RLIKE '(zz)' LIMIT 5;
Name | Age | Salary | Team
---------------+-----+---------+------------------
Jordan Adams | 21 | 1404600 | Memphis Grizzlies
Tony Allen | 34 | 5158539 | Memphis Grizzlies
Chris Andersen | 37 | 5000000 | Memphis Grizzlies
Matt Barnes | 36 | 3542500 | Memphis Grizzlies
Vince Carter | 39 | 4088019 | Memphis Grizzlies
Find players with a Roman numeral suffix¶
Use $
to match only the end of the string
nba=> SELECT "Name","Age","Salary","Team" FROM nba WHERE "Name" RLIKE '[XCLVMI]$';
Name | Age | Salary | Team
--------------------+-----+---------+------------------
Glenn Robinson III | 22 | 1100000 | Indiana Pacers
Johnny O'Bryant III | 23 | 845059 | Milwaukee Bucks
Frank Kaminsky III | 23 | 2612520 | Charlotte Hornets
Find players with just one middle name¶
nba=> SELECT "Name","Age","Salary","Team" FROM nba WHERE "Name" RLIKE '^[a-zA-Z]+ [a-zA-Z]+ [a-zA-Z]+$';
Name | Age | Salary | Team
---------------------+-----+---------+----------------------
James Michael McAdoo | 23 | 845059 | Golden State Warriors
Metta World Peace | 36 | 947276 | Los Angeles Lakers
Glenn Robinson III | 22 | 1100000 | Indiana Pacers
Frank Kaminsky III | 23 | 2612520 | Charlotte Hornets