- Categories:
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.