2026年10月16日に最終更新

REGEXP_EXTRACT_ALL

説明

REGEXP_EXTRACT_ALL 関数は、指定された文字列 str に対して正規表現でマッチングを行い、指定された pattern の最初のサブパターンに一致する部分をすべて抽出します。この関数はパターンに一致した部分を表す文字列を返します。パターンは入力文字列 str の一部に完全に一致する必要があります。一致する部分がない場合、またはパターンにサブパターンが含まれていない場合は、空文字列を返します。

文字集合を使ったマッチングでは、Utf-8 標準の文字クラスを使用してください。これにより、さまざまな言語の文字を正しく識別して処理できます。

'pattern' が有効な正規表現ではない場合は、エラーが発生します。

デフォルトでサポートされる文字マッチング用クラス: https://github.com/google/re2/wiki/Syntax

Doris では、セッション変数 enable_extended_regex（デフォルトは false）を使用して、先読みや後読みなどの高度なゼロ幅アサーションを有効にできます。

セッション変数 enable_extended_regex を true に設定した場合にサポートされる文字マッチングの種類: https://www.boost.org/doc/libs/latest/libs/regex/doc/html/boost_regex/syntax/perl_syntax.html

構文

REGEXP_EXTRACT_ALL(<str>, <pattern>)

パラメータ

パラメータ	説明
`<str>`	String 型のパラメータです。正規表現によるマッチングの対象となる入力文字列を指定します。文字列リテラル、または文字列データを含むテーブルのカラムへの参照を指定できます。
`<pattern>`	String 型のパラメータです。入力文字列とのマッチングに使用する正規表現パターンを指定します。パターンには、文字クラス、量指定子、サブパターンなど、さまざまな正規表現の構文を含めることができます。

戻り値

いずれかの引数が NULL の場合は NULL を返します。

指定された正規表現の最初のサブパターンに一致する、入力文字列の部分を表す文字列を返します。戻り値の型は String です。一致する部分がない場合、またはパターンにサブパターンが含まれていない場合は、空文字列を返します。

配列を返す関数については、REGEXP_EXTRACT_ALL_ARRAY を参照してください。

デフォルトの動作:

デフォルト設定	動作
`.` は改行に一致する	デフォルトでは、`.` は `\n`（改行）に一致します。
大文字と小文字を区別する	マッチングでは大文字と小文字を区別します。
`^`/`$` は文字列全体の境界に一致する	`^` は文字列の先頭にのみ、`$` は文字列の末尾にのみ一致し、各行の先頭や末尾には一致しません。
貪欲な量指定子	デフォルトでは、`*` や `+` などは可能な限り長く一致します。
UTF-8	文字列は UTF-8 として処理されます。

パターン修飾子:

pattern の先頭に (?flags) を付けると、デフォルトの動作を変更できます。(?im) のように複数の修飾子を組み合わせることができます。(?-s) のように - を付けると、対応するオプションが無効になります。

パターン修飾子は、デフォルトの正規表現エンジンを使用する場合にのみ有効です。enable_extended_regex=true を有効にしてゼロ幅アサーション（(?<=...)、(?=...) など）を使用すると、クエリは Boost.Regex エンジンで処理され、修飾子が期待どおりに動作しないことがあります。これらを併用しないことを推奨します。

フラグ	意味
`(?i)`	大文字と小文字を区別しないマッチング
`(?-i)`	大文字と小文字を区別する（デフォルト）
`(?s)`	`.` が改行に一致する（デフォルトで有効）
`(?-s)`	`.` が改行に一致しない
`(?m)`	複数行モード: `^` は各行の先頭に、`$` は各行の末尾に一致する
`(?-m)`	単一行モード: `^`/`$` は文字列全体の境界に一致する（デフォルト）
`(?U)`	非貪欲な量指定子: `*` や `+` などは可能な限り短く一致する
`(?-U)`	貪欲な量指定子（デフォルト）: `*` や `+` などは可能な限り長く一致する

例

'C' の前後にある小文字の基本的なマッチングです。この例では、パターン ([[:lower:]]+)C([[:lower:]]+) は、一文字以上の小文字、'C'、一文字以上の小文字が続く部分に一致します。'C' より前にある最初のサブパターン ([[:lower:]]+) は 'b' に一致するため、結果は ['b'] です。

SELECT regexp_extract_all('AbCdE', '([[:lower:]]+)C([[:lower:]]+)');

+--------------------------------------------------------------+
| regexp_extract_all('AbCdE', '([[:lower:]]+)C([[:lower:]]+)') |
+--------------------------------------------------------------+
| ['b']                                                        |
+--------------------------------------------------------------+

文字列内の複数箇所へのマッチングです。このパターンは文字列内の二箇所に一致します。最初の一致では最初のサブパターンが 'b' に、次の一致では 'f' に一致します。そのため、結果は ['b', 'f'] です。

SELECT regexp_extract_all('AbCdEfCg', '([[:lower:]]+)C([[:lower:]]+)');

+-----------------------------------------------------------------+
| regexp_extract_all('AbCdEfCg', '([[:lower:]]+)C([[:lower:]]+)') |
+-----------------------------------------------------------------+
| ['b','f']                                                       |
+-----------------------------------------------------------------+

キーと値のペアからキーを抽出します。パターンは文字列内のキーと値のペアに一致します。最初のサブパターンがキーをキャプチャするため、結果は ['abc', 'def', 'ghi'] です。

SELECT regexp_extract_all('abc=111, def=222, ghi=333','("[^"]+"|\\w+)=("[^"]+"|\\w+)');

+--------------------------------------------------------------------------------+
| regexp_extract_all('abc=111, def=222, ghi=333', '("[^"]+"|\w+)=("[^"]+"|\w+)') |
+--------------------------------------------------------------------------------+
| ['abc','def','ghi']                                                            |
+--------------------------------------------------------------------------------+

漢字へのマッチングです。パターン (\p{Han}+)(.+) では、最初のサブパターン (\p{Han}+) が一文字以上の漢字に一致するため、結果は ['这是一段中文'] です。

select regexp_extract_all('这是一段中文 This is a passage in English 1234567', '(\\p{Han}+)(.+)');

+------------------------------------------------------------------------------------------------+
| regexp_extract_all('这是一段中文 This is a passage in English 1234567', '(\p{Han}+)(.+)')       |
+------------------------------------------------------------------------------------------------+
| ['这是一段中文']                                                                               |
+------------------------------------------------------------------------------------------------+

データを挿入して REGEXP_EXTRACT_ALL を使用する

CREATE TABLE test_regexp_extract_all (
    id INT,
    text_content VARCHAR(255),
    pattern VARCHAR(255)
) PROPERTIES ("replication_num"="1");


INSERT INTO test_regexp_extract_all VALUES
(1, 'apple1, banana2, cherry3', '([a-zA-Z]+)\\d'),
(2, 'red#123, blue#456, green#789', '([a-zA-Z]+)#\\d+'),
(3, 'hello@example.com, world@test.net', '([a-zA-Z]+)@');


SELECT id, regexp_extract_all(text_content, pattern) AS extracted_data
FROM test_regexp_extract_all;

+------+----------------------+
| id   | extracted_data       |
+------+----------------------+
|    1 | ['apple', 'banana', 'cherry'] |
|    2 | ['red', 'blue', 'green']     |
|    3 | ['hello', 'world']           |
+------+----------------------+

一致する部分がない場合は空文字列を返す

SELECT REGEXP_EXTRACT_ALL('ABC', '(\\d+)');

+-------------------------------------+
| REGEXP_EXTRACT_ALL('ABC', '(\\d+)') |
+-------------------------------------+
|                                     |
+-------------------------------------+

絵文字へのマッチング

SELECT REGEXP_EXTRACT_ALL('👩‍💻,👨‍🚀', '(💻|🚀)');

+--------------------------------------------------------------+
| REGEXP_EXTRACT_ALL('👩‍💻,👨‍🚀', '(💻|🚀)')                      |
+--------------------------------------------------------------+
| ['💻','🚀']                                                 |
+--------------------------------------------------------------+

'Str' が NULL の場合は NULL を返す

SELECT regexp_extract_all(NULL, '([a-z]+)');

+--------------------------------------+
| regexp_extract_all(NULL, '([a-z]+)') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+

'pattern' が NULL の場合は NULL を返す

SELECT regexp_extract_all('Hello World', NULL);

+-----------------------------------------+
| regexp_extract_all('Hello World', NULL) |
+-----------------------------------------+
| NULL                                    |
+-----------------------------------------+

すべてのパラメータが NULL の場合は NULL を返す

SELECT regexp_extract_all(NULL,NULL);

+-------------------------------+
| regexp_extract_all(NULL,NULL) |
+-------------------------------+
| NULL                          |
+-------------------------------+

pattern が有効な正規表現ではない場合はエラーが発生します。

SELECT regexp_extract_all('hello (world) 123', '([[:alpha:]+');

ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.2)[INVALID_ARGUMENT]Could not compile regexp pattern: ([[:alpha:]+
Error: missing ]: [[:alpha:]+

高度な正規表現

SELECT REGEXP_EXTRACT_ALL('ID:AA-1,ID:BB-2,ID:CC-3', '(?<=ID:)([A-Z]{2}-\\d)');

ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[INVALID_ARGUMENT]Invalid regex pattern: (?<=ID:)([A-Z]{2}-\d). Error: invalid perl operator: (?<

SET enable_extended_regex = true;
SELECT REGEXP_EXTRACT_ALL('ID:AA-1,ID:BB-2,ID:CC-3', '(?<=ID:)([A-Z]{2}-\\d)');

+-------------------------------------------------------------------------+
| REGEXP_EXTRACT_ALL('ID:AA-1,ID:BB-2,ID:CC-3', '(?<=ID:)([A-Z]{2}-\\d)') |
+-------------------------------------------------------------------------+
| ['AA-1','BB-2','CC-3']                                                  |
+-------------------------------------------------------------------------+

パターン修飾子

大文字と小文字を区別しないマッチング: (?i) を指定すると、大文字と小文字を区別せずにマッチングします

SELECT REGEXP_EXTRACT_ALL('Hello hello HELLO', '(hello)') AS case_sensitive,
       REGEXP_EXTRACT_ALL('Hello hello HELLO', '(?i)(hello)') AS case_insensitive;

+----------------+---------------------------+
| case_sensitive | case_insensitive          |
+----------------+---------------------------+
| ['hello']      | ['Hello','hello','HELLO'] |
+----------------+---------------------------+

複数行モード: (?m) を指定すると、^ と $ が各行の先頭と末尾に一致します

SELECT REGEXP_EXTRACT_ALL('foo\nbar\nbaz', '^([a-z]+)') AS single_line,
       REGEXP_EXTRACT_ALL('foo\nbar\nbaz', '(?m)^([a-z]+)') AS multi_line;

+-------------+---------------------+
| single_line | multi_line          |
+-------------+---------------------+
| ['foo']     | ['foo','bar','baz'] |
+-------------+---------------------+

貪欲なマッチングと非貪欲なマッチング: (?U) を指定すると、量指定子は可能な限り短く一致します

SELECT REGEXP_EXTRACT_ALL('aXbXcXd', '(a.*X)') AS greedy,
       REGEXP_EXTRACT_ALL('aXbXcXd', '(?U)(a.*X)') AS non_greedy;

+----------+------------+
| greedy   | non_greedy |
+----------+------------+
| ['aXbXcX'] | ['aX']   |
+----------+------------+

説明​

構文​

パラメータ​

戻り値​

例​

説明

構文

パラメータ

戻り値

例