バージョン: 4.x

REGEXP_EXTRACT_ALL

説明

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

文字セットマッチングを処理する際は、Utf-8標準の文字クラスを使用する必要があることに注意してください。これにより、関数が異なる言語のさまざまな文字を正しく識別し処理できることが保証されます。

'pattern'が許可されたregexp正規表現でない場合、エラーを投げます；

サポートされる文字マッチクラス：https://github.com/google/re2/wiki/Syntax

構文

REGEXP_EXTRACT_ALL(<str>, <pattern>)

パラメータ

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

戻り値

この関数は、指定された正規表現の最初のサブパターンにマッチする入力文字列の部分を表す文字列の配列を返します。戻り値の型はString値の配列です。マッチするものが見つからない場合、またはパターンにサブパターンがない場合は、空の配列が返されます。

デフォルトの動作:

デフォルト設定	動作
`.`が改行にマッチ	`.`はデフォルトで`\n`（改行）にマッチできます。
大文字小文字を区別	マッチングは大文字小文字を区別します。
`^`/`$`が文字列全体の境界にマッチ	`^`は文字列の開始のみにマッチし、`$`は文字列の終端のみにマッチし、行の開始/終端にはマッチしません。
貪欲な量詞	`*`、`+`などはデフォルトで可能な限り多くマッチします。
UTF-8	文字列はUTF-8として処理されます。

パターン修飾子:

patternの前に(?flags)を付けることで、デフォルトの動作を上書きできます。複数の修飾子を組み合わせることができます（例：(?im)）。-プレフィックスは対応するオプションを無効にします（例：(?-s)）。

フラグ	意味
`(?i)`	大文字小文字を区別しないマッチング
`(?-i)`	大文字小文字を区別する（デフォルト）
`(?s)`	`.`が改行にマッチ（デフォルトで有効）
`(?-s)`	`.`が改行にマッチしない
`(?m)`	複数行モード：`^`が各行の開始にマッチ、`$`が各行の終端にマッチ
`(?-m)`	単一行モード：`^`/`$`が文字列全体の境界にマッチ（デフォルト）
`(?U)`	非貪欲な量詞：`*`、`+`などが可能な限り少なくマッチ
`(?-U)`	貪欲な量詞（デフォルト）：`*`、`+`などが可能な限り多くマッチ

例

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

mysql> SELECT regexp_extract_all('AbCdE', '([[:lower:]]+)C([[:lower:]]+)');
+--------------------------------------------------------------+
| regexp_extract_all('AbCdE', '([[:lower:]]+)C([[:lower:]]+)') |
+--------------------------------------------------------------+
| ['b']                                                        |
+--------------------------------------------------------------+

文字列内の複数のマッチ。ここでは、パターンが文字列内の2つの部分にマッチします。最初のマッチでは最初のサブパターンが'b'にマッチし、2番目のマッチでは最初のサブパターンが'f'にマッチします。そのため結果は['b', 'f']となります。

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

キー・バリューペアからキーを抽出します。パターンは文字列内のキー・バリューペアにマッチします。最初のサブパターンがキーをキャプチャするため、結果はキーの配列 ['abc', 'def', 'ghi'] になります。

mysql> 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}+)で1つ以上の中国語文字を最初にマッチさせるため、結果は['这是一段中文']となります。

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

絵文字マッチ

mysql> 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:]+

パターン修飾子

大文字小文字を区別しない: (?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'] |
+-------------+---------------------+

貪欲 vs 非貪欲：(?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']   |
+----------+------------+

説明​

構文​

パラメータ​

戻り値​

例​

説明

構文

パラメータ

戻り値

例