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

REGEXP_LIKE¶

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

REGEXP_LIKE 与 [ NOT ] LIKE 函数类似，但使用 POSIX 扩展的正则表达式而不是 SQL LIKE 模式语法。它支持比 LIKE 更复杂的匹配条件。

小技巧

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

别名:: :doc:`rlike`（第 1 种语法）

语法¶

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

实参¶

必填：

subject

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

pattern

要匹配的模式。

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

可选：

parameters

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


参数	描述
`c`	区分大小写的匹配
`i`	不区分大小写的匹配
`m`	多行模式
`e`	提取子匹配项
`s`	POSIX 通配符 `.` 与 `\n` 匹配

默认：c

有关更多详细信息，请参阅为正则表达式指定参数。

返回¶

Returns a BOOLEAN value or NULL:

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

使用说明¶

该函数在两端隐式锚定一个模式（例如 '' 自动变为 '^$'，并且 'ABC' 自动变为 '^ABC$'）。要匹配任何以 ABC 开头的字符串，模式将是 '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

有关正则表达式的其他示例，请参阅 [ NOT ] 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);

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.*');

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

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

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

+---------------+
| 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');

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

使用通配符执行区分大小写的查询：

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

+------+
| 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+');

+---------------+
| 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+');

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

反斜杠字符 (\) 是转义字符。有关更多信息，请参阅在以单引号括起的字符串常量中指定正则表达式。

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');

The first inserted value doesn't contain special characters.

To show the data, query the table:

SELECT * FROM regex_special_characters;

+-------+
| 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.*');

+-------+
| 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, '.*\\$.*');

+-------+
| 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, '.*(\\.|\\\\).*');

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