Categories:: String Functions (Regular Expressions)

REGEXP_LIKE¶

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

REGEXP_LIKE 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: RLIKE (1st syntax)

REGEXP_COUNT , REGEXP_INSTR , REGEXP_REPLACE , REGEXP_SUBSTR , REGEXP_SUBSTR_ALL

ILIKE , LIKE

Syntax¶

REGEXP_LIKE( <subject> , <pattern> [ , <parameters> ] )

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¶

Create a table with names of cities:

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

Execute a case-sensitive query with a wildcard:

SELECT * FROM cities WHERE REGEXP_LIKE(city, 'san.*');
+------+
| CITY |
|------|
+------+

Execute a case-insensitive query with a wildcard:

SELECT * FROM cities WHERE REGEXP_LIKE(city, 'san.*', 'i');
+---------------+
| CITY          |
|---------------|
| San Francisco |
| San Jose      |
+---------------+

For additional examples of regular expressions, see: REGEXP.

To search for a wildcard character, you need to escape the wildcard character. For more information about wildcard characters, see Specifying Regular Expressions in Single-Quoted String Constants.