类别：

字符串函数（正则表达式）

REGEXP_COUNT

Returns the number of times that a pattern occurs in a string.

语法

REGEXP_COUNT( <subject> ,
              <pattern>
                [ , <position>
                  [ , <parameters> ]
                ]
)
Copy

实参

必填：

subject

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

pattern

要匹配的模式。

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

可选：

position

Number of characters from the beginning of the string where the function starts searching for matches. The value must be a positive integer.

默认值：``1``（搜索匹配项时，从左边的第一个字符开始）

parameters

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

参数

描述

c

区分大小写的匹配

i

不区分大小写的匹配

m

多行模式

e

提取子匹配项

s

Single-line mode POSIX wildcard character . matches \n

默认：c

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

返回

返回类型 NUMBER 的值。如果任何实参为 NULL，则返回 NULL。

使用说明

有关正则表达式函数，请参阅 一般使用说明。

排序规则详细信息

Arguments with collation specifications currently aren't supported.

示例

The following example counts occurrences of the word was. You can use the \b metacharacter to indicate a word boundary. In the following example, matching begins at the first character in the string w and ends at the last character in the string s, and so doesn't match words that contain the string (such as washing):

SELECT REGEXP_COUNT('It was the best of times, it was the worst of times',
                    '\\bwas\\b',
                    1) AS result;
Copy
+--------+
| RESULT |
|--------|
|      2 |
+--------+

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

SELECT REGEXP_COUNT('Excelence', 'e', 1, 'i') AS e_in_excelence;
Copy
+----------------+
| E_IN_EXCELENCE |
|----------------|
|              4 |
+----------------+

以下示例说明了重叠出现的情况。创建表并插入数据：

CREATE OR REPLACE TABLE overlap (id NUMBER, a STRING);
INSERT INTO overlap VALUES (1,',abc,def,ghi,jkl,');
INSERT INTO overlap VALUES (2,',abc,,def,,ghi,,jkl,');

SELECT * FROM overlap;
Copy
+----+----------------------+
| ID | A                    |
|----+----------------------|
|  1 | ,abc,def,ghi,jkl,    |
|  2 | ,abc,,def,,ghi,,jkl, |
+----+----------------------+

运行查询，使用 REGEXP_COUNT 计算每一行中出现以下模式的次数：标点符号后是数字和字母，然后是标点符号。

SELECT id,
       REGEXP_COUNT(a,
                    '[[:punct:]][[:alnum:]]+[[:punct:]]',
                    1,
                    'i') AS result
  FROM overlap;
Copy
+----+--------+
| ID | RESULT |
|----+--------|
|  1 |      2 |
|  2 |      4 |
+----+--------+

The remaining examples use the data in the following table:

CREATE OR REPLACE TABLE regexp_count_demo (dt DATE, messages VARCHAR);

INSERT INTO regexp_count_demo (dt, messages) VALUES
  ('10-AUG-2025','ER-6842,LG-230,LG-150,ER-3379,ER-6210'),
  ('11-AUG-2025','LG-272,LG-605,LG-683,ER-5577'),
  ('12-AUG-2025','ER-2207,LG-551,LG-826,ER-6842');

SELECT * FROM regexp_count_demo;
Copy
+------------+---------------------------------------+
| DT         | MESSAGES                              |
|------------+---------------------------------------|
| 2025-08-10 | ER-6842,LG-230,LG-150,ER-3379,ER-6210 |
| 2025-08-11 | LG-272,LG-605,LG-683,ER-5577          |
| 2025-08-12 | ER-2207,LG-551,LG-826,ER-6842         |
+------------+---------------------------------------+

The following query returns the total number of messages for each day by searching for the delimiter (,) and adding one to the total:

SELECT dt,
       REGEXP_COUNT(messages, ',') + 1 AS message_count
  FROM regexp_count_demo;
Copy
+------------+---------------+
| DT         | MESSAGE_COUNT |
|------------+---------------|
| 2025-08-10 |             5 |
| 2025-08-11 |             4 |
| 2025-08-12 |             4 |
+------------+---------------+

Assume that errors always begin with ER followed by a hyphen and a four-digit number. The following query counts the number of errors for each day:

SELECT dt,
       REGEXP_COUNT(messages, '\\bER-[0-9]{4}') AS number_of_errors
  FROM regexp_count_demo;
Copy
+------------+------------------+
| DT         | NUMBER_OF_ERRORS |
|------------+------------------|
| 2025-08-10 |                3 |
| 2025-08-11 |                1 |
| 2025-08-12 |                2 |
+------------+------------------+
语言: 中文