REGEXP_EXTRACT
説明
これは、指定された文字列STRに対して正規表現マッチを実行し、指定されたパターンに適合するPOS番目のマッチング部分を抽出するために使用される関数です。関数がマッチング結果を返すためには、パターンがSTRの一部と正確にマッチする必要があります。
マッチが見つからない場合、空の文字列が返されます。 文字セットマッチングを処理する際には、Utf-8標準文字クラスを使用する必要があることに注意してください。これにより、関数が異なる言語からの様々な文字を正しく識別し、処理できることが保証されます。
strパラメータは'string'型で、正規表現マッチングの対象となる文字列を表します。
patternパラメータは'string'型で、対象となる正規表現パターンを表します。
posパラメータは'integer'型で、文字列内で正規表現マッチの検索を開始する位置を指定するために使用されます。位置は1から始まり、このパラメータは必須です。
patternが許可されたregexp正規表現でない場合、エラーを発生させます；
サポートされる文字マッチクラス：https://github.com/google/re2/wiki/Syntax
構文
REGEXP_EXTRACT(<str>, <pattern>, <pos>)
パラメータ
| パラメータ | 説明 |
|---|---|
<str> | 正規表現マッチングを行う対象の列。'string'型です。 |
<pattern> | 対象となる正規表現パターン。'string'型です。 |
<pos> | 文字列内で正規表現マッチの検索を開始する位置を指定するために使用されるパラメータ。文字列内の文字位置を表す整数値です（1から開始）。posは必須です。 |
戻り値
パターンにマッチした部分。Varchar型です。マッチが見つからない場合は、空の文字列が返されます。
デフォルトの動作:
| デフォルト設定 | 動作 |
|---|---|
.が改行にマッチ | .はデフォルトで\n（改行）にマッチできます。 |
| 大文字小文字を区別 | マッチングは大文字小文字を区別します。 |
^/$は文字列全体の境界にマッチ | ^は文字列の開始のみにマッチし、$は終端のみにマッチします。行の開始/終端にはマッチしません。 |
| 貪欲量詞 | *、+などはデフォルトで可能な限り多くマッチします。 |
| UTF-8 | 文字列はUTF-8として処理されます。 |
パターン修飾子:
patternの前に(?flags)を付けることで、デフォルトの動作を上書きできます。複数の修飾子は組み合わせることができます（例：(?im)）。-プレフィックスは対応するオプションを無効にします（例：(?-s)）。
| フラグ | 意味 |
|---|---|
(?i) | 大文字小文字を区別しないマッチング |
(?-i) | 大文字小文字を区別する（デフォルト） |
(?s) | .が改行にマッチする（デフォルトで有効） |
(?-s) | .は改行にマッチしない |
(?m) | マルチラインモード：^は各行の開始にマッチし、$は各行の終端にマッチ |
(?-m) | シングルラインモード：^/$は文字列全体の境界にマッチ（デフォルト） |
(?U) | 非貪欲量詞：*、+などは可能な限り少なくマッチ |
(?-U) | 貪欲量詞（デフォルト）：*、+などは可能な限り多くマッチ |
例
最初にマッチした部分を抽出します。この例では、正規表現([[:lower:]]+)C([[:lower:]]+)は、1つ以上の小文字の後に'C'が続き、その後に1つ以上の小文字が続く文字列の部分にマッチします。'C'の前の最初のキャプチャグループ([[:lower:]]+)は'b'にマッチするため、結果は'b'になります。
mysql> SELECT regexp_extract('AbCdE', '([[:lower:]]+)C([[:lower:]]+)', 1);
+-------------------------------------------------------------+
| regexp_extract('AbCdE', '([[:lower:]]+)C([[:lower:]]+)', 1) |
+-------------------------------------------------------------+
| b |
+-------------------------------------------------------------+
2番目にマッチする部分を抽出します。ここでは、'C'の後の2番目のキャプチャグループ([[:lower:]]+)が'd'にマッチするため、結果は'd'となります。
mysql> SELECT regexp_extract('AbCdE', '([[:lower:]]+)C([[:lower:]]+)', 2);
+-------------------------------------------------------------+
| regexp_extract('AbCdE', '([[:lower:]]+)C([[:lower:]]+)', 2) |
+-------------------------------------------------------------+
| d |
+-------------------------------------------------------------+
中国語文字にマッチします。パターン(\p{Han}+)(.+)は、最初に1つ以上の中国語文字(\p{Han}+)にマッチし、その後文字列の残りの部分((.+))にマッチします。2番目のキャプチャグループは文字列の非中国語部分にマッチするため、結果は'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:]]+)（1つ以上の数字）は文字列 '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:]]+
パターン修飾子
大文字小文字を区別しない: (?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 |
+-------------+------------+
貪欲 vs 非貪欲: (?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 |
+--------+------------+