String Functions (Regular Expressions)


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.


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

See also




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

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




Subject to match.


Pattern to match.



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.


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('','\\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('','[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 |