Categories:

String Functions (Regular Expressions)

RLIKE

Returns true if the subject matches the specified pattern. Both inputs must be text expressions.

RLIKE is similar to the LIKE function, but with POSIX extended regular expressions instead of SQL LIKE pattern syntax. It supports more complex matching conditions than LIKE.

Aliases

REGEXP (2nd syntax) , REGEXP_LIKE (1st syntax)

See also

ILIKE , LIKE

REGEXP_COUNT , REGEXP_INSTR , REGEXP_REPLACE , REGEXP_SUBSTR

Syntax

-- 1st syntax
RLIKE( <subject> , <pattern> [ , <parameters> ] )

-- 2nd syntax
<subject> RLIKE <pattern>

Arguments

Required:

subject

Subject to match.

pattern

Pattern to match.

Optional:

parameters

String of one or more characters that specifies the parameters used for searching for matches. Supported values:

c , i , m , e , s

For more details, see Parameters.

Default: c

Usage Notes

  • The function implicitly anchors a pattern at both ends (i.e. '' automatically becomes '^$', and 'ABC' automatically becomes '^ABC$'). To match any string starting with ABC, the pattern would be 'ABC.*'.

  • For additional usage notes, see General Usage Notes.

Collation Details

Arguments with collation specifications are currently not supported.

Examples

Set up for examples:

CREATE OR REPLACE TABLE rlike_ex(city varchar(20));
INSERT INTO rlike_ex VALUES ('Sacramento'), ('San Francisco'), ('San Jose'), (null);

First Syntax

Case-insensitive pattern matching with wildcards:

SELECT * FROM rlike_ex WHERE RLIKE(city, 'san.*', 'i');

+---------------+
| CITY          |
|---------------|
| San Francisco |
| San Jose      |
+---------------+

Return a true/false value when testing the format of phone numbers and email addresses:

-- Escape the backslash character (\) in \w and \d

SELECT rlike('800-456-7891','[2-9]\\d{2}-\\d{3}-\\d{4}') FROM dual;

+---------------------------------------------------+
| RLIKE('800-456-7891','[2-9]\\D{2}-\\D{3}-\\D{4}') |
|---------------------------------------------------|
| True                                              |
+---------------------------------------------------+

SELECT rlike('jsmith@email.com','\\w+@[a-zA-Z_]+?\.[a-zA-Z]{2,3}') FROM dual;

+-------------------------------------------------------------+
| RLIKE('JSMITH@EMAIL.COM','\\W+@[A-ZA-Z_]+?\.[A-ZA-Z]{2,3}') |
|-------------------------------------------------------------|
| True                                                        |
+-------------------------------------------------------------+

-- Alternatively, rewrite the statements and avoid sequences that rely on the backslash character

SELECT rlike('800-456-7891','[2-9][0-9]{2}-[0-9]{3}-[0-9]{4}') FROM dual;

+---------------------------------------------------------+
| RLIKE('800-456-7891','[2-9][0-9]{2}-[0-9]{3}-[0-9]{4}') |
|---------------------------------------------------------|
| True                                                    |
+---------------------------------------------------------+

SELECT rlike('jsmith@email.com','[a-zA-Z_]+@[a-zA-Z_]+?\.[a-zA-Z]{2,3}') FROM dual;

+-------------------------------------------------------------------+
| RLIKE('JSMITH@EMAIL.COM','[A-ZA-Z_]+@[A-ZA-Z_]+?\.[A-ZA-Z]{2,3}') |
|-------------------------------------------------------------------|
| True                                                              |
+-------------------------------------------------------------------+

Second Syntax

Case-sensitive pattern matching:

SELECT * FROM rlike_ex WHERE city RLIKE 'San.* [fF].*';

+---------------+
| CITY          |
|---------------|
| San Francisco |
+---------------+