Categories:

String functions (regular expressions)

REGEXP_LIKE

Performs a comparison to determine whether a string matches a specified pattern. Both inputs must be text expressions.

REGEXP_LIKE is similar to the [ NOT ] LIKE function, but with POSIX extended regular expressions instead of SQL LIKE pattern syntax. It supports more complex matching conditions than LIKE.

Tip

You can use the search optimization service to improve the performance of queries that call this function. For details, see Search Optimization Service.

Aliases:

[ NOT ] RLIKE (1st syntax)

See also: String functions (regular expressions)

Syntax

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

Arguments

Required:

subject

The string to search for matches.

pattern

Pattern to match.

For guidelines on specifying patterns, see String functions (regular expressions).

Optional:

parameters

String of one or more characters that specifies the parameters used for searching for matches. Supported values:

Parameter

Description

c

Case-sensitive matching

i

Case-insensitive matching

m

Multi-line mode

e

Extract submatches

s

POSIX wildcard character . matches \n

Default: c

For more details, see Specifying the parameters for the regular expression.

Returns

Returns a BOOLEAN or NULL. The value is TRUE if there is a match. Otherwise, returns FALSE. Returns NULL if any argument is NULL.

Usage Notes

  • The function implicitly anchors a pattern at both ends (for example, '' 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);
Copy

Run a case-sensitive query with a wildcard:

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

Run a case-insensitive query with a wildcard:

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

For additional examples of regular expressions, see [ NOT ] 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.

Language: English