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.

Collation:

Not supported

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 '^$').
  • For additional usage notes, see General Usage Notes.

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 |
+---------------+