REGEXP_EXTRACT_OR_NULL
説明
テキスト文字列から対象の正規表現パターンにマッチする最初の部分文字列を抽出し、表現グループインデックスに基づいて特定のグループを抽出します。
文字セットマッチングを処理する際は、Utf-8標準文字クラスを使用する必要があることに注意してください。これにより、関数が異なる言語の様々な文字を正しく識別し、処理できることが保証されます。
Apache Doris 3.0.2以降でサポート
'pattern'が許可されたregexp正規表現でない場合、エラーをスローします
デフォルトでサポートされている文字マッチクラス : 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_OR_NULL(<str>, <pattern>, <pos>)
パラメータ
| パラメータ | 説明 |
|---|---|
<str> | 文字列パラメータ。正規表現マッチングが実行される対象のテキスト文字列を表します。この文字列は任意の文字の組み合わせを含むことができ、関数は |
<pattern> | 文字列パラメータ。対象となる正規表現パターンです。このパターンには様々な正規表現のメタ文字や文字クラスを含めることができ、マッチングする部分文字列のルールを正確に定義します。 |
<pos> | 整数パラメータ。抽出する表現グループのインデックスを示します。インデックスは1から始まります。 |
戻り値
文字列型を返し、結果は<pattern>にマッチする部分となります。
入力<pos>が0の場合、最初にマッチした部分文字列全体を返します。
入力<pos>が無効（負の値または表現グループの数を超過）の場合、NULLを返します。
正規表現のマッチングが失敗した場合、NULLを返します。
<pos> < 0の場合、NULLを返します。
pos > <str>の長さの場合、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'で区切られた1つ以上の小文字の連続を探します。インデックス1のグループは最初の小文字の連続に対応するため、'b'が返されます。
SELECT REGEXP_EXTRACT_OR_NULL('123AbCdExCx', '([[:lower:]]+)C([[:lower:]]+)', 1);
+---------------------------------------------------------------------------+
| REGEXP_EXTRACT_OR_NULL('123AbCdExCx', '([[:lower:]]+)C([[:lower:]]+)', 1) |
+---------------------------------------------------------------------------+
| b |
+---------------------------------------------------------------------------+
マッチした部分文字列全体を返します。
SELECT REGEXP_EXTRACT_OR_NULL('123AbCdExCx', '([[:lower:]]+)C([[:lower:]]+)', 0);
+---------------------------------------------------------------------------+
| REGEXP_EXTRACT_OR_NULL('123AbCdExCx', '([[:lower:]]+)C([[:lower:]]+)', 0) |
+---------------------------------------------------------------------------+
| bCd |
+---------------------------------------------------------------------------+
無効なグループインデックス。パターンにはグループが2つしかないため、インデックス5は範囲外となり、NULLが返されます。
SELECT REGEXP_EXTRACT_OR_NULL('123AbCdExCx', '([[:lower:]]+)C([[:lower:]]+)', 5);
+---------------------------------------------------------------------------+
| REGEXP_EXTRACT_OR_NULL('123AbCdExCx', '([[:lower:]]+)C([[:lower:]]+)', 5) |
+---------------------------------------------------------------------------+
| NULL |
+---------------------------------------------------------------------------+
マッチしない正規表現です。文字列 'AbCdE' にはパターンに完全に一致する部分がありません
SELECT REGEXP_EXTRACT_OR_NULL('AbCdE', '([[:lower:]]+)C([[:upper:]]+)', 1);
+---------------------------------------------------------------------+
| REGEXP_EXTRACT_OR_NULL('AbCdE', '([[:lower:]]+)C([[:upper:]]+)', 1) |
+---------------------------------------------------------------------+
| NULL |
+---------------------------------------------------------------------+
中国語文字のマッチング。パターン(\p{Han}+)(.+)は、最初に1つ以上の中国語文字にマッチし、その後残りの文字にマッチします。インデックス2のグループは、中国語文字の後の文字列の非中国語部分を表します。
select REGEXP_EXTRACT_OR_NULL('这是一段中文 This is a passage in English 1234567', '(\\p{Han}+)(.+)', 2);
+---------------------------------------------------------------------------------------------------------+
| REGEXP_EXTRACT_OR_NULL('这是一段中文 This is a passage in English 1234567', '(\\p{Han}+)(.+)', 2) |
+---------------------------------------------------------------------------------------------------------+
| This is a passage in English 1234567 |
+---------------------------------------------------------------------------------------------------------+
テーブルへのデータ挿入と抽出の実行
CREATE TABLE test_regexp_extract_or_null (
id INT,
text_column VARCHAR(255),
pattern_column VARCHAR(255),
position_column INT
) PROPERTIES ("replication_num"="1");
INSERT INTO test_regexp_extract_or_null VALUES
(1, 'abc123def', '([a-z]+)([0-9]+)([a-z]+)', 2),
(2, 'Hello World', '([A-Z][a-z]+) ([A-Z][a-z]+)', 0),
(3, '123-456-789', '([0-9]{3})-([0-9]{3})-([0-9]{3})', 3),
(4, 'example@example.com', '([a-z]+)@([a-z]+)\\.([a-z]+)', 1),
(5, '测试文本 test text', '(\\p{Han}+) (.+)', 1);
SELECT id, REGEXP_EXTRACT_OR_NULL(text_column, pattern_column, position_column) AS extracted_result
FROM test_regexp_extract_or_null
ORDER BY id;
+------+-----------------+
| id | extracted_result|
+------+-----------------+
| 1 | 123 |
| 2 | Hello World |
| 3 | 789 |
| 4 | example |
| 5 | 测试文本 |
+------+-----------------+
Emoji ケース
SELECT regexp_extract_or_null('😀😊😎', '😀|😊|😎',0);
+------------------------------------------------------------+
| regexp_extract_or_null('😀😊😎', '😀|😊|😎',0) |
+------------------------------------------------------------+
| 😀 |
+------------------------------------------------------------+
'str'がNULLの場合、NULLを返す
SELECT REGEXP_EXTRACT_OR_NULL(NULL, '([a-z]+)', 1);
+---------------------------------------------+
| REGEXP_EXTRACT_OR_NULL(NULL, '([a-z]+)', 1) |
+---------------------------------------------+
| NULL |
+---------------------------------------------+
'pattern'がNULLの場合、NULLを返す
SELECT REGEXP_EXTRACT_OR_NULL('Hello World', NULL, 1);
+------------------------------------------------+
| REGEXP_EXTRACT_OR_NULL('Hello World', NULL, 1) |
+------------------------------------------------+
| NULL |
+------------------------------------------------+
'pos'がNULLの場合、NULLを返す
SELECT REGEXP_EXTRACT_OR_NULL('Hello World', '([a-z]+)', NULL);
+---------------------------------------------------------+
| REGEXP_EXTRACT_OR_NULL('Hello World', '([a-z]+)', NULL) |
+---------------------------------------------------------+
| NULL |
+---------------------------------------------------------+
全てのパラメータがNULLの場合、NULLを返します；
SELECT REGEXP_EXTRACT_OR_NULL(NULL,NULL,NULL);
+----------------------------------------+
| REGEXP_EXTRACT_OR_NULL(NULL,NULL,NULL) |
+----------------------------------------+
| NULL |
+----------------------------------------+
patternが正規表現として許可されていない場合、エラーをスローします；
mysql> SELECT REGEXP_EXTRACT_OR_NULL('123AbCdExCx', '([[:lower:]]+)C([[]ower:]]+)', 1);
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.2)[INVALID_ARGUMENT]Could not compile regexp pattern: ([[:lower:]]+)C([[:lower:]+)
Error: missing ]: [[:lower:]+)
高度な正規表現
SELECT regexp_extract_or_null('foo123bar', '(?<=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_or_null('foo123bar', '(?<=foo)(\\d+)(?=bar)', 1);
+-----------------------------------------------------------------+
| regexp_extract_or_null('foo123bar', '(?<=foo)(\\d+)(?=bar)', 1) |
+-----------------------------------------------------------------+
| 123 |
+-----------------------------------------------------------------+
パターン修飾子
大文字小文字を区別しないマッチング: (?i) はマッチ時に大文字小文字を無視する
SELECT REGEXP_EXTRACT_OR_NULL('Hello World', '(hello)', 1) AS case_sensitive,
REGEXP_EXTRACT_OR_NULL('Hello World', '(?i)(hello)', 1) AS case_insensitive;
+----------------+------------------+
| case_sensitive | case_insensitive |
+----------------+------------------+
| NULL | Hello |
+----------------+------------------+
デフォルトでは.は改行にマッチします。.が改行にマッチしないようにするには(?-s)を使用してください
SELECT REGEXP_EXTRACT_OR_NULL('foo\nbar', '^(.+)$', 1) AS dot_match_nl,
REGEXP_EXTRACT_OR_NULL('foo\nbar', '(?-s)^(.+)$', 1) AS dot_not_match_nl;
+--------------+------------------+
| dot_match_nl | dot_not_match_nl |
+--------------+------------------+
| foo
bar | NULL |
+--------------+------------------+
マルチラインモード: (?m) は ^ と $ を各行の開始/終了にマッチさせます
SELECT REGEXP_EXTRACT_OR_NULL('foo\nbar', '^(bar)', 1) AS single_line,
REGEXP_EXTRACT_OR_NULL('foo\nbar', '(?m)^(bar)', 1) AS multi_line;
+-------------+------------+
| single_line | multi_line |
+-------------+------------+
| NULL | bar |
+-------------+------------+
貪欲 vs 非貪欲: (?U) は量詞を可能な限り少なくマッチさせます
SELECT REGEXP_EXTRACT_OR_NULL('aXbXc', '(a.*X)', 1) AS greedy,
REGEXP_EXTRACT_OR_NULL('aXbXc', '(?U)(a.*X)', 1) AS non_greedy;
+--------+------------+
| greedy | non_greedy |
+--------+------------+
| aXbX | aX |
+--------+------------+
