に最終更新
REGEXP_EXTRACT_OR_NULL
説明
テキスト文字列から対象の正規表現パターンに一致する最初の部分文字列を見つけ、指定したキャプチャグループを抽出します。
文字集合で照合する場合は、UTF-8 に対応した標準の文字クラスを使用してください。これにより、さまざまな言語の文字を正しく識別して処理できます。
備考
Apache Doris 3.0.2 以降でサポートされています。
パターンが有効な正規表現でない場合は、エラーが発生します。
デフォルトでサポートされる文字クラス: https://github.com/google/re2/wiki/Syntax
セッション変数 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 は最初の小文字の並びに対応するため、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}+)(.+) は、まず一文字以上の漢字に一致し、その後に残りの文字に一致します。この例では、グループ 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 | 测试文本 |
+------+-----------------+
絵文字の例
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 |
+-------------+------------+
貪欲な照合と非貪欲な照合: (?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 |
+--------+------------+