类别：: 字符串函数（正则表达式）

REGEXP_LIKE¶

执行比较以确定字符串是否与指定模式相匹配。两个输入都必须是文本表达式。

REGEXP_LIKE is similar to the LIKE function, but with POSIX extended regular expressions (link removed) instead of SQL LIKE pattern syntax. REGEXP_LIKE supports more complex matching conditions than LIKE.

小技巧

您可以使用搜索优化服务，提高用于调用此函数的查询的性能。有关详细信息，请参阅 Search optimization service。

别名:: RLIKE (1st syntax)

语法¶

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

Copy

实参¶

必填：

subject

用于搜索匹配项的字符串。

pattern

要匹配的模式。

有关指定模式的准则，请参阅字符串函数（正则表达式）。

可选：

parameters

包含一个或多个字符的字符串，指定用于搜索匹配项的参数。支持的值：

参数	描述
`c`	区分大小写的匹配
`i`	不区分大小写的匹配
`m`	多行模式
`e`	提取子匹配项
`s`	Single-line mode POSIX wildcard character `.` matches `\n`

默认：c

For more information, see 为正则表达式指定参数.

返回¶

Returns a BOOLEAN value or NULL:

Returns TRUE if there is a match.
Returns FALSE if there isn't a match.
如果任何实参为 NULL，则返回 NULL。

使用说明¶

The function implicitly anchors a pattern at both ends (for example, '' automatically becomes '^$', and 'ABC' automatically becomes '^ABC$'). For example, to match any string starting with ABC, the pattern is 'ABC.*'.
反斜杠字符 (\) 是转义字符。有关更多信息，请参阅在以单引号括起的字符串常量中指定正则表达式。
有关更多使用说明，请参阅一般使用说明，了解正则表达式函数。

排序规则详细信息¶

Arguments with collation specifications currently aren't supported.

示例¶

The following examples use the REGEXP_LIKE function:

Run basic regular expression queries on strings
Run regular expression queries on strings with special characters

For additional examples of regular expressions, see REGEXP.

Run basic regular expression queries on strings¶

创建一个包含城市名称的表：

CREATE OR REPLACE TABLE cities(city VARCHAR(20));

INSERT INTO cities VALUES
  ('Sacramento'),
  ('San Francisco'),
  ('San Luis Obispo'),
  ('San Jose'),
  ('Santa Barbara'),
  ('Palo Alto'),
  (NULL);

Copy

You can use .* as a wildcard to match as many characters as possible. The following example matches the pattern Fran anywhere in the string value:

SELECT * FROM cities WHERE REGEXP_LIKE(city, '.*Fran.*');

Copy

+---------------+
| CITY          |
|---------------|
| San Francisco |
+---------------+

The following example uses the i parameter for case-insensitive matching:

SELECT * FROM cities WHERE REGEXP_LIKE(city, '.*fran.*', 'i');

Copy

+---------------+
| CITY          |
|---------------|
| San Francisco |
+---------------+

To find a pattern that matches the beginning of a string value, run a query that uses a wildcard:

SELECT * FROM cities WHERE REGEXP_LIKE(city, 'san.*', 'i');

Copy

+-----------------+
| CITY            |
|-----------------|
| San Francisco   |
| San Luis Obispo |
| San Jose        |
| Santa Barbara   |
+-----------------+

To run a case-sensitive query with a wildcard, omit the i parameter:

SELECT * FROM cities WHERE REGEXP_LIKE(city, 'san.*');

Copy

+------+
| CITY |
|------|
+------+

You can use the \w+ metacharacter to match one word and \s metacharacter to match one whitespace character, such as a space or a tab. The following query searches for the values that include one word, followed by a whitespace character, followed by one word:

SELECT * FROM cities WHERE REGEXP_LIKE(city, '\\w+\\s\\w+');

Copy

+---------------+
| CITY          |
|---------------|
| San Francisco |
| San Jose      |
| Santa Barbara |
| Palo Alto     |
+---------------+

The output for the query doesn't include San Luis Obispo because that value has three words with a space between the first and second words instead of only two words with a space in between them.

In a regular expression, you can often use an uppercase metacharacter to negate the meaning of a lowercase metacharacter. For example, run a query that searches for the values that don't include a whitespace character between two words by using the \S metacharacter:

SELECT * FROM cities WHERE REGEXP_LIKE(city, '\\w+\\S\\w+');

Copy

+------------+
| CITY       |
|------------|
| Sacramento |
+------------+

Run regular expression queries on strings with special characters¶

The examples in this section search for values with special characters, which are characters other than a-z, A-Z, underscore (“_”), or decimal digit.

To search for a metacharacter, escape the metacharacter. For more information, see 在以单引号括起的字符串常量中指定正则表达式.

Create a table, and then insert some values with special characters:

CREATE OR REPLACE TABLE regex_special_characters(v VARCHAR(20));

INSERT INTO regex_special_characters VALUES
  ('Snow'),
  ('Sn.ow'),
  ('Sn@ow'),
  ('Sn$ow'),
  ('Sn\\ow');

Copy

The first inserted value doesn't contain special characters.

To show the data, query the table:

SELECT * FROM regex_special_characters;

Copy

+-------+
| V     |
|-------|
| Snow  |
| Sn.ow |
| Sn@ow |
| Sn$ow |
| Sn\ow |
+-------+

You can search for any special character by using the \W Perl backslash-sequence, which searches for characters that aren't "word" characters. For example, the following query searches for the values in the table that have special characters:

SELECT *
  FROM regex_special_characters
  WHERE REGEXP_LIKE(v, '.*\\W.*');

Copy

+-------+
| V     |
|-------|
| Sn.ow |
| Sn@ow |
| Sn$ow |
| Sn\ow |
+-------+

To search for metacharacters in a single-quoted string constant, you must escape the metacharacter with two backslashes. For example, the following query searches for the values that contain the $ metacharacter:

SELECT *
  FROM regex_special_characters
  WHERE REGEXP_LIKE(v, '.*\\$.*');

Copy

+-------+
| V     |
|-------|
| Sn$ow |
+-------+

If you search for a backslash, an additional backslash escape character is required. For example, the following query searches for the values that contain the \ or the . metacharacter:

SELECT *
  FROM regex_special_characters
  WHERE REGEXP_LIKE(v, '.*(\\.|\\\\).*');

Copy

+-------+
| V     |
|-------|
| Sn.ow |
| Sn\ow |
+-------+