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)

See also: String Functions (Regular Expressions)

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.

⇧
Back to top