REGEXP_EXTRACT
説明
この関数は、指定した文字列 STR に正規表現を適用し、指定したパターンに一致する POS 番目の部分を抽出します。一致結果を返すには、パターンが STR の一部と正確に一致する必要があります。
一致する部分が見つからない場合は、空文字列を返します。 文字集合を照合する際は、UTF-8 標準の文字クラスを使用してください。これにより、さまざまな言語の文字を正しく識別して処理できます。
str パラメータは文字列型で、正規表現による照合の対象となる文字列を表します。
pattern パラメータは文字列型で、照合する正規表現パターンを表します。
pos パラメータは整数型で、正規表現の一致を検索し始める文字列内の位置を指定します。位置は 1 から始まり、このパラメータは必須です。
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(<str>, <pattern>, <pos>)
パラメータ
| パラメータ | 説明 |
|---|---|
<str> | 正規表現で照合する列。文字列型です。 |
<pattern> | 照合する正規表現パターン。文字列型です。 |
<pos> | 正規表現の一致を検索し始める文字列内の位置を指定するパラメータ。文字列内の文字位置を表す整数値で、1 から始まります。pos の指定は必須です。 |
戻り値
パターンに一致する部分を VARCHAR 型で返します。一致する部分が見つからない場合は、空文字列を返します。いずれかの引数が NULL の場合は、NULL を返します。
既定の動作:
| 既定の設定 | 動作 |
|---|---|
. は改行に一致 | デフォルトでは . は \n（改行）に一致します。 |
| 大文字と小文字を区別 | 一致判定では大文字と小文字を区別します。 |
^/$ は文字列全体の境界に一致 | ^ は文字列の先頭にのみ、$ は文字列の末尾にのみ一致し、各行の先頭や末尾には一致しません。 |
| 貪欲な量指定子 | デフォルトでは * や + などは可能な限り長く一致します。 |
| UTF-8 | 文字列は UTF-8 として処理されます。 |
パターン修飾子:
pattern の先頭に (?flags) を付けると、既定の動作を変更できます。複数の修飾子を組み合わせることができます（例: (?im)）。- を付けると、対応するオプションが無効になります（例: (?-s)）。
パターン修飾子は、デフォルトの正規表現エンジンを使用する場合にのみ有効です。enable_extended_regex=true を設定してゼロ幅アサーション（例: (?<=...)、(?=...)）を使用すると、クエリは Boost.Regex エンジンで処理され、修飾子が期待どおりに動作しない場合があります。両者を併用しないことを推奨します。
| フラグ | 意味 |
|---|---|
(?i) | 大文字と小文字を区別せずに照合します。 |
(?-i) | 大文字と小文字を区別して照合します（デフォルト）。 |
(?s) | . が改行に一致します（デフォルトで有効）。 |
(?-s) | . が改行に一致しなくなります。 |
(?m) | 複数行モード: ^ は各行の先頭に、$ は各行の末尾に一致します。 |
(?-m) | 単一行モード: ^/$ は文字列全体の境界に一致します（デフォルト）。 |
(?U) | 非貪欲な量指定子: * や + などは可能な限り短く一致します。 |
(?-U) | 貪欲な量指定子（デフォルト）: * や + などは可能な限り長く一致します。 |
例
最初の一致部分を抽出します。この例では、正規表現 ([[:lower:]]+)C([[:lower:]]+) は、一文字以上の小文字、C、さらに一文字以上の小文字が続く部分に一致します。C より前の最初のキャプチャグループ ([[:lower:]]+) は b に一致するため、結果は b です。
mysql> SELECT regexp_extract('AbCdE', '([[:lower:]]+)C([[:lower:]]+)', 1);
+-------------------------------------------------------------+
| regexp_extract('AbCdE', '([[:lower:]]+)C([[:lower:]]+)', 1) |
+-------------------------------------------------------------+
| b |
+-------------------------------------------------------------+
二番目の一致部分を抽出します。ここでは、C より後の二番目のキャプチャグループ ([[:lower:]]+) が d に一致するため、結果は d です。
mysql> SELECT regexp_extract('AbCdE', '([[:lower:]]+)C([[:lower:]]+)', 2);
+-------------------------------------------------------------+
| regexp_extract('AbCdE', '([[:lower:]]+)C([[:lower:]]+)', 2) |
+-------------------------------------------------------------+
| d |
+-------------------------------------------------------------+
中国語の文字に一致させます。パターン (\p{Han}+)(.+) は、まず一文字以上の中国語の文字（\p{Han}+）に一致し、続いて文字列の残りの部分（(.+)）に一致します。二番目のキャプチャグループは中国語以外の部分に一致するため、結果は 'This is a passage in English 1234567' です。
mysql> select regexp_extract('这是一段中文 This is a passage in English 1234567', '(\\p{Han}+)(.+)', 2);
+-----------------------------------------------------------------------------------------------+
| regexp_extract('这是一段中文 This is a passage in English 1234567', '(\p{Han}+)(.+)', 2) |
+-----------------------------------------------------------------------------------------------+
| This is a passage in English 1234567 |
+-----------------------------------------------------------------------------------------------+
値をテーブルに挿入して照合します。この例では、テーブルにデータを挿入し、保存されたパターンと位置に基づいて、REGEXP_EXTRACT 関数で保存された文字列から一致部分を抽出します。
CREATE TABLE test_table_for_regexp_extract (
id INT,
text_data VARCHAR(500),
pattern VARCHAR(100),
pos INT
) PROPERTIES ("replication_num"="1");
INSERT INTO test_table_for_regexp_extract VALUES
(1, 'AbCdE', '([[:lower:]]+)C([[:lower:]]+)', 1),
(2, 'AbCdE', '([[:lower:]]+)C([[:lower:]]+)', 2),
(3, '这是一段中文 This is a passage in English 1234567', '(\\p{Han}+)(.+)', 2);
SELECT id, regexp_extract(text_data, pattern, pos) as extract_result FROM test_table_for_regexp_extract ORDER BY id;
+------+----------------+
| id | extract_result |
+------+----------------+
| 1 | b |
| 2 | d |
| 3 | This is a passage in English 1234567 |
+------+----------------+
一致する部分がないパターンを試します。パターン ([[:digit:]]+)（一桁以上の数字）は文字列 'AbCdE' のどの部分にも一致しないため、空文字列が返されます。
SELECT regexp_extract('AbCdE', '([[:digit:]]+)', 1);
+------------------------------------------------+
| regexp_extract('AbCdE', '([[:digit:]]+)', 1) |
+------------------------------------------------+
| |
+------------------------------------------------+
絵文字のテストケース
SELECT regexp_extract('Text 😊 More 😀', '😊|😀',0);
+------------------------------------------------------+
| regexp_extract('Text 😊 More 😀', '😊|😀',0) |
+------------------------------------------------------+
| 😊 |
+------------------------------------------------------+
'str' が NULL の場合は NULL を返します。
mysql> SELECT REGEXP_EXTRACT(NULL, '([a-z]+)', 1);
+-------------------------------------+
| REGEXP_EXTRACT(NULL, '([a-z]+)', 1) |
+-------------------------------------+
| NULL |
+-------------------------------------+
'pattern' が NULL の場合は NULL を返します。
mysql> SELECT REGEXP_EXTRACT('Hello World', NULL, 1);
+----------------------------------------+
| REGEXP_EXTRACT('Hello World', NULL, 1) |
+----------------------------------------+
| NULL |
+----------------------------------------+
'pos' が NULL の場合は NULL を返します。
mysql> SELECT REGEXP_EXTRACT('Hello World', '([a-z]+)', NULL);
+-------------------------------------------------+
| REGEXP_EXTRACT('Hello World', '([a-z]+)', NULL) |
+-------------------------------------------------+
| NULL |
+-------------------------------------------------+
すべてのパラメータが NULL の場合は NULL を返します。
mysql> SELECT REGEXP_EXTRACT(NULL, NULL, NULL);
+----------------------------------+
| REGEXP_EXTRACT(NULL, NULL, NULL) |
+----------------------------------+
| NULL |
+----------------------------------+
pattern が有効な正規表現でない場合は、エラーになります。
SELECT regexp_extract('AbCdE', '([[:digit:]]+', 1);
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.2)[INVALID_ARGUMENT]Could not compile regexp pattern: ([[:digit:]]+
Error: missing ): ([[:digit:]]+
高度な正規表現
SELECT regexp_extract('foo123bar456baz', '(?<=foo)(\\d+)(?=bar)', 1);
-- ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[INVALID_ARGUMENT]Invalid regex pattern: (?<=foo)(\d+)(?=bar). Error: invalid perl operator: (?<
SET enable_extended_regex = true;
SELECT regexp_extract('foo123bar456baz', '(?<=foo)(\\d+)(?=bar)', 1);
+---------------------------------------------------------------+
| regexp_extract('foo123bar456baz', '(?<=foo)(\\d+)(?=bar)', 1) |
+---------------------------------------------------------------+
| 123 |
+---------------------------------------------------------------+
パターン修飾子
大文字と小文字を区別しない照合: (?i) を指定すると、大文字と小文字を区別せずに照合します。
SELECT REGEXP_EXTRACT('Hello World', '(hello)', 1) AS case_sensitive,
REGEXP_EXTRACT('Hello World', '(?i)(hello)', 1) AS case_insensitive;
+----------------+------------------+
| case_sensitive | case_insensitive |
+----------------+------------------+
| | Hello |
+----------------+------------------+
デフォルトでは . は改行に一致します。(?-s) を使用すると、. が改行に一致しなくなります。
SELECT REGEXP_EXTRACT('foo\nbar', '^(.+)$', 1) AS dot_match_nl,
REGEXP_EXTRACT('foo\nbar', '(?-s)^(.+)$', 1) AS dot_not_match_nl;
+--------------+------------------+
| dot_match_nl | dot_not_match_nl |
+--------------+------------------+
| foo
bar | |
+--------------+------------------+
複数行モード: (?m) を指定すると、^ と $ は各行の先頭と末尾に一致します。
SELECT REGEXP_EXTRACT('foo\nbar', '^(bar)', 1) AS single_line,
REGEXP_EXTRACT('foo\nbar', '(?m)^(bar)', 1) AS multi_line;
+-------------+------------+
| single_line | multi_line |
+-------------+------------+
| | bar |
+-------------+------------+
貪欲な一致と非貪欲な一致: (?U) を指定すると、量指定子は可能な限り短く一致します。
SELECT REGEXP_EXTRACT('aXbXc', '(a.*X)', 1) AS greedy,
REGEXP_EXTRACT('aXbXc', '(?U)(a.*X)', 1) AS non_greedy;
+--------+------------+
| greedy | non_greedy |
+--------+------------+
| aXbX | aX |
+--------+------------+