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)

## 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.