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.
There is no default escape character.
If you use the backslash as an escape character, then you must specify escape the backslash in the ESCAPE clause. For example, the following command specifies that the escape character is the backslash, and then uses that escape character to search for ‘%’ as a literal (without the escape character, the ‘%’ would be treated as a wildcard):
```
'SOMETHING%' LIKE '%\\%%' ESCAPE '\\';
```
For examples of using escape characters, and in particular the backslash as an escape character, see the examples for the LIKE function.

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