REGEXP_SUBSTR

REGEXP_SUBSTRは、MariaDBで文字列に対して正規表現パターンを用いた検索を行い、そのパターンに一致する部分文字列を抽出するための関数です。従来のSUBSTRING関数が固定の位置や区切り文字に基づいて部分文字列を抽出するのに対し、REGEXP_SUBSTRはより複雑なパターンマッチングによって柔軟な文字列抽出を可能にします。

構文 (Syntax)

REGEXP_SUBSTR(対象文字列, パターン [, 開始位置 [, 出現回数 [, マッチタイプ]]])

• マッチタイプ (match_type, オプション)
  マッチングの動作を制御する文字列です。以下のオプションを組み合わせて指定できます。
  • 'i' または '(?i)': 大文字・小文字を区別しないマッチングを行います。
  • 'c' または '(?-i)': 大文字・小文字を区別するマッチングを行います。（デフォルトの照合順序がケースインセンシティブな場合でも、このオプションで強制できます。）
  • 'm' または '(?m)': マルチラインモードを有効にします。これにより、^（行の先頭）と$（行の末尾）が文字列全体の先頭/末尾だけでなく、改行文字の直後/直前にもマッチするようになります。
  • 'n' または '(?n)': 改行文字を任意の文字にマッチさせるドット（.）に含めません。デフォルトではドットは改行文字にもマッチします。
• 出現回数 (occurrence, オプション)
  パターンに一致する部分が複数ある場合に、何番目の一致を返すかを指定します。デフォルトは1（最初の一致）です。1未満の値を指定すると、1として扱われます。
• 開始位置 (position, オプション)
  検索を開始する文字列内の位置を指定します。デフォルトは1（文字列の先頭）です。1未満の値を指定すると、1として扱われます。
• パターン (pattern)
  検索する正規表現パターンです。VARCHARやTEXT型で指定します。
• 対象文字列 (subject)
  検索対象となる元の文字列です。VARCHARやTEXT型が想定されます。

戻り値 (Return Value)

• 対象文字列またはパターンがNULLの場合、NULLを返します。
• パターンに一致する部分が見つからない場合は、空の文字列（''）を返します。
• 指定されたパターンに一致する部分文字列を返します。

特徴と利点 (Features and Benefits)

• ケースセンシティブ/インセンシティブ制御
  マッチタイプオプションを使用することで、大文字・小文字の区別を柔軟に制御できます。
• 柔軟な抽出
  開始位置と出現回数を指定することで、文字列の特定の部分から検索を開始したり、複数の一致から任意の一致を抽出したりできます。
• Perl互換正規表現 (PCRE) のサポート
  MariaDB 10.0.5以降では、Perl互換正規表現ライブラリ（PCRE）が使用されており、より高度な正規表現構文（先読み、後読み、再帰パターンなど）が利用可能です。
• 高度なパターンマッチング
  LIKE演算子よりもはるかに強力な正規表現を使用して、複雑な文字列パターンを検索・抽出できます。例えば、特定の形式のメールアドレス、URL、電話番号などを抽出するのに役立ちます。

基本的な部分文字列の抽出

文字列から数字の連続を抽出します。

SELECT REGEXP_SUBSTR('abc123xyz456', '[0-9]+');
-- 結果: '123'

2番目の数字の連続を抽出

SELECT REGEXP_SUBSTR('abc123xyz456def', '[0-9]+', 1, 2);
-- 結果: '456'

大文字・小文字を区別しない抽出

'apple'という文字列を大文字・小文字を区別せずに検索します。

SELECT REGEXP_SUBSTR('Apple pie is delicious.', 'apple', 1, 1, 'i');
-- 結果: 'Apple'

URLの抽出

文字列の中からURLを抽出します。

SELECT REGEXP_SUBSTR('Visit our website at https://www.example.com/page or http://test.org.', 'https?://[^ ]+');
-- 結果: 'https://www.example.com/page'

メールアドレスの抽出

SELECT REGEXP_SUBSTR('My email is [email protected].', '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,4}');
-- 結果: '[email protected]'

---

パターンが一致しない（結果が空文字列 '' になる）

最もよくある問題です。

• トラブルシューティング
  
  • 正規表現の確認
    まず、使用している正規表現が正しいかどうかを、オンラインの正規表現テスター（例: regex101.com, regexr.com）で検証してください。対象文字列のサンプルを貼り付けて、パターンが正しくマッチするかを確認します。
  • エスケープシーケンス
    正規表現の特殊文字（例: ., *, +, ?, (, ), [, ], {, }, \, |, ^, $）をリテラルとして扱いたい場合は、\でエスケープする必要があります（例: ドット.を文字のドットとして扱いたい場合は\.）。MariaDBの文字列リテラル内では、さらに\自体をエスケープする必要がある場合があるため、\\と二重にすることもあります。
  • マッチタイプ オプション
    大文字・小文字を区別しない検索を行いたい場合は、'i'（または'(?i)'）をマッチタイプに指定しているか確認します。
  • マルチラインモード
    対象文字列に改行が含まれ、^や$が各行の先頭/末尾にマッチすることを期待する場合は、'm'（または'(?m)'）をマッチタイプに指定しているか確認します。また、ドット.が改行にもマッチするようにしたい場合は、'n'オプションを使わないようにします。
  • 部分的な一致
    厳密なパターンにせず、まずはより緩いパターンで試してみて、徐々に厳しくしていくことで、どこが問題か特定しやすくなります。
• 原因
  指定した正規表現パターンが、対象文字列のどこにもマッチしない場合に発生します。
  • 正規表現の構文が間違っている。
  • パターンが厳密すぎ、意図しない文字やスペースなどが含まれている。
  • 大文字・小文字の区別（ケースセンシティブ/インセンシティブ）が期待と異なる。
  • アンカー（^, $）の使い方が適切でない。
  • 改行文字の扱い（ドット.が改行にマッチするかどうか）が期待と異なる。

想定と異なる部分文字列が抽出される

パターンは一致するが、期待した部分ではない、あるいは意図しない部分まで含まれてしまう場合。

• トラブルシューティング
  
  • 貪欲性と非貪欲性
    量指定子（*, +, ?など）はデフォルトで貪欲です。つまり、可能な限り長くマッチしようとします。例えば、<.*>は最初の<から最後の>までをマッチさせます。短いマッチが必要な場合は、量指定子の後に?を付けて非貪欲にします（例: <.*?>）。
  • 開始位置と出現回数
    これらのオプションの値を変更してみて、結果がどう変わるかを確認します。特に、文字列の途中から検索を開始したり、複数の一致から特定の一致を抽出したりしたい場合に重要です。
• 原因
  
  • 正規表現の「貪欲性（Greediness）」または「非貪欲性（Laziness）」の理解不足。
  • キャプチャグループの指定ミス（REGEXP_SUBSTRは通常、全体のマッチを返すため、キャプチャグループの直接指定はできませんが、パターン自体の問題として）。
  • 開始位置や出現回数の指定が意図と異なる。

エラーメッセージが発生する

MariaDBから直接エラーメッセージが返される場合。

• 原因とトラブルシューティング
  
  • 不正な正規表現構文
    
    • 原因
      正規表現の記述に誤りがある。例えば、閉じられていない括弧(や角括弧[、無効なエスケープシーケンス\などが含まれる場合。
    • トラブルシューティング
      正規表現を注意深く確認し、オンラインテスターでエラーが出ないか検証します。特に、\の後に予期しない文字が続いている場合は、エスケープ漏れか余計なエスケープがある可能性があります。
  • 不正なUTF-8バイトシーケンス
    
    • 原因
      対象文字列またはパターンに含まれるデータが、MariaDBの文字セット（特にUTF-8）と一致しない不正なバイトシーケンスを含んでいる場合に発生します。これは、データのインポート時や、異なるエンコーディングのデータが混在している場合に起こりやすいです。
    • トラブルシューティング
      
      • 文字セットの確認
        テーブルやカラムの文字セットがデータの内容と一致しているか確認します。SHOW CREATE TABLE your_table_name;で確認できます。
      • データのクリーンアップ
        不正な文字を特定し、削除または置換することを検討します。HEX()関数を使って文字列のバイト列を確認し、不正なパターンを見つけることができます。
      • CONVERT()関数
        必要に応じて、CONVERT(対象文字列 USING utf8mb4)のようにして、明示的に文字列をUTF-8に変換してからREGEXP_SUBSTRに渡すことを試します。ただし、根本的なデータの破損を解決するわけではありません。
• 一般的なエラーメッセージ例
  
  • ERROR 1139 (42000): Got error 'pcre_compile: invalid UTF-8 octet sequence'
  • ERROR 1139 (42000): Got error 'pcre_compile: unrecognized character after \ at offset ...'
  • ERROR 1139 (42000): Got error 'pcre_exec: invalid UTF-8 byte sequence in the subject string'

特に大量のデータに対してREGEXP_SUBSTRを使用すると、クエリが遅くなることがあります。

• トラブルシューティング
  
  • 正規表現の最適化
    
    • 可能な限り具体的にパターンを記述し、曖昧なマッチング（例: .*）を減らす。
    • 代替パターン（|）を効率的に使う。
    • 不必要なキャプチャグループ（()）やバックリファレンスを避ける。
  • インデックスの使用
    
    • もし可能であれば、REGEXP_SUBSTRを使う前に、WHERE句で通常のLIKEや等価比較を使ってデータを絞り込み、処理対象の行数を減らします。これにより、インデックスが利用され、REGEXP_SUBSTRが適用される行数が少なくなります。
    • 関数インデックス（MariaDB 10.3以降）の検討: 特定の状況下では、関数インデックスが役立つ場合がありますが、REGEXP_SUBSTRのような複雑な関数ではその効果は限定的です。
  • アプリケーション側での処理
    大量のテキストデータ処理が必要な場合は、データベース側ではなく、アプリケーションのコード（Python, Java, PHPなど）で正規表現処理を行うことも検討します。アプリケーション側の言語は、正規表現処理においてより柔軟で高性能なライブラリを提供している場合があります。
  • クエリの分解
    複雑な抽出を複数ステップに分け、中間結果を一時テーブルに格納してから次の処理を行うなど、クエリを分割することも検討します。
• 原因
  
  • 正規表現の処理は計算コストが高い。
  • インデックスが利用できない。REGEXP_SUBSTRのような関数は、ほとんどの場合、カラムのインデックスを効果的に利用できません。
  • 非効率な正規表現パターン（例: .*の多用）。

• テスト環境での検証
  複雑な正規表現を使用する前に、必ずテスト環境で十分な検証を行います。特に、エッジケースや異常なデータパターンに対する挙動を確認することが重要です。
• MariaDBのバージョン
  使用しているMariaDBのバージョンを確認します。REGEXP_SUBSTRなどの正規表現関数は、MariaDB 10.0.5以降で導入されました。それ以前のバージョンでは使用できません。また、PCREの機能やパフォーマンスもバージョンによって改善されている可能性があります。

---

基本的な文字列の抽出

最も基本的な使い方です。特定のパターンに最初にマッチする部分を抽出します。

例
文字列の中から最初の連続する数字を抽出する。

SELECT
  REGEXP_SUBSTR('ユーザーID: 12345, 注文番号: 9876', '[0-9]+') AS extracted_number;

結果

extracted_number
12345

解説
[0-9]+ は「1つ以上の数字」にマッチします。文字列の先頭から検索し、最初に見つかった数字の並びである「12345」を抽出します。

特定の出現回数の部分文字列を抽出

REGEXP_SUBSTR の第4引数を使って、複数マッチする場合の何番目のマッチを抽出するかを指定できます。

例
文字列の中から2番目の連続する数字を抽出する。

SELECT
  REGEXP_SUBSTR('ユーザーID: 12345, 注文番号: 9876', '[0-9]+', 1, 2) AS second_number;

結果

second_number
9876

解説
1, 2 は「文字列の先頭（1文字目）から検索を開始し、2番目にマッチするパターンを抽出する」という意味です。これにより、「9876」が抽出されます。

REGEXP_SUBSTR の第5引数（マッチタイプ）に 'i' を指定することで、大文字・小文字を区別しない検索が可能です。

例
大文字・小文字を区別せずに「apple」という単語を抽出する。

SELECT
  REGEXP_SUBSTR('Apple pie is delicious.', 'apple', 1, 1, 'i') AS extracted_word;

結果

extracted_word
Apple

解説
'i' オプションにより、パターン 'apple' は 'Apple' にもマッチします。

URLのドメイン名を抽出

より実践的な例として、URLからドメイン名だけを抽出する方法です。

例
完全なURLからドメイン名（例: example.com）を抽出する。

SELECT
  REGEXP_SUBSTR('https://www.example.com/path/to/page?id=123', '://([^/]+)/?', 1, 1) AS domain_name;

結果

domain_name

解説

• /?: オプションのスラッシュにマッチします。
• ([^/]+): :// の後に続く、スラッシュ以外の文字が1回以上続く部分にマッチします。この括弧 () はキャプチャグループですが、REGEXP_SUBSTR は全体のマッチを返すため、ここではwww.example.comの部分を含めてマッチします。
• ://: http:// または https:// の部分にマッチ。

注意
REGEXP_SUBSTR はPCRE（Perl Compatible Regular Expressions）をベースにしているため、多くの正規表現構文が利用可能です。しかし、直接キャプチャグループの内容だけを抽出する機能は持っていません。もしキャプチャグループだけが必要な場合は、REGEXP_REPLACE（特定のグループを抽出するようには設計されていないが、間接的に可能）や、アプリケーション側での処理が必要になります。

上記の例では、([^/]+) が実際に抽出したいドメイン部分になりますが、REGEXP_SUBSTR はマッチした全体（://www.example.com/ または ://www.example.com）を返します。この例では、www.example.com を抽出するために、パターンを調整して欲しい部分だけがマッチするようにしています。

特定のタグ内のコンテンツを抽出（非貪欲マッチの利用）

HTMLのような構造化されたテキストから、特定のタグ内のコンテンツを抽出する際に、非貪欲マッチングが役立ちます。

例
<span> タグ内のテキストを抽出する。

SELECT
  REGEXP_SUBSTR('これは <span>重要な</span> メッセージです。別の <span>情報</span> もあります。', '<span>(.*?)</span>') AS span_content;

結果

span_content
<span>重要な</span>

解説

• </span>: リテラルの</span>タグにマッチ。
• (.*?): . は任意の文字、* は0回以上の繰り返し、? は非貪欲（最短マッチ）を意味します。これにより、最初の</span>が現れるまでの最短の文字列にマッチします。
• <span>: リテラルの<span>タグにマッチ。

非貪欲マッチ *? を使用しない場合 (.*)、文字列中の最初の <span> から最後の </span> までがマッチしてしまいます。

特定の形式の日付文字列を抽出し、その形式が正しいかどうかの簡易的なバリデーションも兼ねます。

例
YYYY-MM-DD 形式の日付を抽出する。

SELECT
  REGEXP_SUBSTR('イベントは2023-10-26に開催されます。', '\\d{4}-\\d{2}-\\d{2}') AS event_date;

結果

event_date
2023-10-26

解説

• -: ハイフンリテラル
• \\d{2}: 数字2桁（月または日）
• \\d{4}: 数字4桁（年）

注意
SQL文字列リテラル内でバックスラッシュ\を正規表現のエスケープ文字として使用する場合、\自体をエスケープする必要があるため、\\と二重に記述します。

---

REGEXP_SUBSTR の代替となる主な方法と、それぞれの適したケースについて説明します。

SUBSTRING / SUBSTR + LOCATE / INSTR

最も基本的な文字列操作関数です。固定の位置や、特定の区切り文字に基づいて部分文字列を抽出する場合に有効です。

• LOCATE(substr, str [, pos]) / INSTR(str, substr): str 内で substr が最初に出現する位置を返します。INSTR は LOCATE(substr, str) と同等です。
• SUBSTRING(str, pos, len) / SUBSTR(str, pos, len): 文字列 str の pos 番目の位置から len 文字分の部分文字列を抽出します。

適したケース

• 正規表現を使うほど複雑なパターンではない場合。
• 抽出したい部分が特定の区切り文字（例: , や -）で囲まれていることが分かっている場合。
• 抽出したい部分が常に文字列の固定位置にある場合。

例
文字列「ITEM-A-12345」から「A-12345」を抽出する（最初のハイフンの後）。

SELECT
    SUBSTRING('ITEM-A-12345', LOCATE('-', 'ITEM-A-12345') + 1) AS extracted_part;

結果

extracted_part
A-12345

例
メールアドレスからドメイン名だけを抽出する（@ と . を区切り文字として）。

SELECT
    SUBSTRING(
        '[email protected]',
        LOCATE('@', '[email protected]') + 1,
        LOCATE('.', '[email protected]', LOCATE('@', '[email protected]') + 1) - (LOCATE('@', '[email protected]') + 1)
    ) AS domain;

結果

domain
example.com

利点
正規表現に比べてシンプルで、パフォーマンスが良いことが多いです。

欠点
複雑なパターンや変動する構造の文字列には対応しにくいです。

SUBSTRING_INDEX

特定の区切り文字に基づいて文字列を分割し、指定したインデックスの部分を抽出する関数です。

• SUBSTRING_INDEX(str, delimiter, count): 文字列 str を delimiter で分割し、count 番目の部分を返します。
  • count が正の場合、左から数えて count 番目の区切り文字までの部分を返します。
  • count が負の場合、右から数えて count 番目の区切り文字までの部分を返します。

適したケース

• CSVのような形式のデータから特定のフィールドを抽出する場合。
• 文字列が明確な区切り文字によって複数のセクションに分かれている場合。

例
ファイルパスからファイル名（拡張子なし）を抽出する。

SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX('/path/to/my_file.txt', '/', -1), '.', 1) AS file_name_no_ext;

結果

file_name_no_ext
my_file

解説

1. SUBSTRING_INDEX('/path/to/my_file.txt', '/', -1): 最後の / 以降（my_file.txt）を取得。
2. SUBSTRING_INDEX('my_file.txt', '.', 1): 最初の . 以前（my_file）を取得。

利点
REGEXP_SUBSTR よりも直感的で、区切り文字ベースの抽出では非常に簡潔に記述できます。

欠点
区切り文字が存在しない、あるいは不規則なパターンには対応できません。

REPLACE / TRIM / LPAD / RPAD など他の文字列操作関数との組み合わせ

これらの関数を組み合わせて、不要な部分を削除したり、特定の部分を抽出したりすることも可能です。

• TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str): 文字列の先頭、末尾、または両方から指定した文字を削除します。
• REPLACE(str, from_str, to_str): 文字列 str 内の from_str を to_str に置換します。

適したケース

• 単純な文字列置換で問題が解決する場合。
• 特定のプレフィックスやサフィックスを削除したい場合。

例
XMLのような文字列から特定のタグを取り除く（簡易的な場合）。

SELECT
    REPLACE(REPLACE('<data>Hello World</data>', '<data>', ''), '</data>', '') AS cleaned_string;

結果

cleaned_string
Hello World

利点
シンプルな文字列操作に適しており、正規表現のオーバーヘッドを避けることができます。

欠点
複雑なパターンやネストされた構造には不向きです。

アプリケーション層での処理

MariaDBのSQL関数だけで抽出が困難な場合、または非常に複雑なロジックが必要な場合は、アプリケーション（PHP, Python, Java, Node.jsなど）のコードで文字列処理を行うことを検討します。

適したケース

• データベーススキーマの正規化が困難な、半構造化データ（JSON, XMLなど）を扱う場合。
• 抽出したデータをさらに加工・変換する必要がある場合。
• プログラミング言語のより高度な正規表現ライブラリ（例: Pythonのreモジュール）や文字列処理機能を利用したい場合。
• データベースの負荷を軽減したい場合。

利点

• 特定の言語に特化した便利なライブラリを利用できる。
• エラーハンドリングやデバッグがしやすい。
• より柔軟なロジックを実装できる。

欠点

• ロジックがデータベース層とアプリケーション層に分散し、管理が複雑になる場合がある。
• データベースとアプリケーション間のデータ転送量が増える可能性がある。

JSON関数 (MariaDB 10.2 以降)

もし対象の文字列がJSON形式であれば、MariaDB 10.2以降で利用可能なJSON関数（JSON_EXTRACTなど）が非常に強力な代替手段となります。

適したケース

• カラムにJSON形式のデータが格納されている場合。

例
JSONデータから特定のフィールドの値を取得する。

-- テーブルにJSONカラムがある場合
SELECT
  JSON_EXTRACT(json_data_column, '$.name') AS user_name
FROM
  your_table;

-- リテラルJSON文字列の場合
SELECT
  JSON_EXTRACT('{"id": 1, "name": "Alice", "email": "[email protected]"}', '$.name') AS user_name;

結果

user_name
Alice

利点
JSONデータのパースと抽出に特化しており、非常に効率的で読みやすいコードになります。

欠点
JSON形式のデータにしか適用できません。

• データベースの負荷分散、高度な処理、言語特有の機能が必要な場合
  アプリケーション層での処理を検討します。
• JSON形式のデータ
  迷わず JSON_EXTRACT などのJSON関数を使用します。
• 非常に複雑なパターン、または変動する構造
  REGEXP_SUBSTR が最も適しています。正規表現の学習コストはかかりますが、それを補って余りある柔軟性を提供します。
• 複雑なパターンだが、正規表現ほどではない、またはパフォーマンスが重要
  他の文字列関数を組み合わせて、正規表現の使用を避ける努力をします。
• 最も単純なケース（固定位置、明確な区切り文字）
  SUBSTRING + LOCATE または SUBSTRING_INDEX を優先的に検討します。パフォーマンスと可読性に優れます。