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: String Functions (Regular Expressions)

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 Specifying the Parameters for the Regular Expression.

Default: c

Returns

The data type of the returned value is BOOLEAN.

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.*'.

  • The backslash character (\) is the escape character. For more information, see Specifying Regular Expressions in Single-Quoted String Constants.

  • For more usage notes, see the General Usage Notes for regular expression functions.

Collation Details

Arguments with collation specifications are currently not supported.

Examples

Run the following commands to set up the data for the examples in this topic:

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

First Syntax

The following example performs case-insensitive pattern matching with wildcards:

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

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

The following examples determine if a string matches the format of a phone number and an email address. In these examples, the regular expressions are specified in dollar-quoted strings to avoid escaping the backslashes in the regular expression.

SELECT RLIKE('800-456-7891', $$[2-9]\d{2}-\d{3}-\d{4}$$) AS matches_phone_number;

+----------------------+
| MATCHES_PHONE_NUMBER |
|----------------------|
| True                 |
+----------------------+

SELECT RLIKE('jsmith@email.com',$$\w+@[a-zA-Z_]+?\.[a-zA-Z]{2,3}$$) AS matches_email_address;

+-----------------------+
| MATCHES_EMAIL_ADDRESS |
|-----------------------|
| True                  |
+-----------------------+

The following examples perform the same matches but use single-quoted string constants to specify the regular expressions.

Because the example uses single-quoted string constants, each backslash must be escaped with another backslash.

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

SELECT RLIKE('800-456-7891','[2-9]\\d{2}-\\d{3}-\\d{4}') AS matches_phone_number;

+----------------------+
| MATCHES_PHONE_NUMBER |
|----------------------|
| True                 |
+----------------------+

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

+-----------------------+
| MATCHES_EMAIL_ADDRESS |
|-----------------------|
| 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}') AS matches_phone_number;

+----------------------+
| MATCHES_PHONE_NUMBER |
|----------------------|
| True                 |
+----------------------+

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

+-----------------------+
| MATCHES_EMAIL_ADDRESS |
|-----------------------|
| True                  |
+-----------------------+

Second Syntax

Case-sensitive pattern matching:

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

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

Additional Examples

For additional examples of regular expressions, see REGEXP.