SQLでのデータ検索・フィルタリングに正規表現を利用することで、より柔軟で高度なデータ操作が可能になります。本記事では、SQLにおける正規表現の使用方法とその応用について詳しく解説します。具体的には、正規表現の基本概念から始め、各データベースシステムで利用可能な正規表現関数、基本および高度な使用例、パフォーマンスの最適化方法、そして実際のケーススタディを通じて、その有用性を探ります。
以下の REGEXP の例は原則 MySQL 8.0/8.4 向けです。PostgreSQL・Oracle・SQL Server の例は明記しています。バックスラッシュはSQL文字列と正規表現の二重の解釈に注意し、MySQLの後方参照例は通常のSQLモードを前提にしています。SIMILAR TO と POSIX正規表現は同じ構文ではありません。
正規表現の基本概念とSQLへの導入
正規表現（Regular Expression）は、文字列のパターンを定義するための特殊な文字列です。これにより、特定の文字列を検索、置換、抽出することが可能になります。正規表現は様々なプログラミング言語やツールで利用できますが、SQLでも正規表現を用いることで高度なパターンマッチングを実現できます。
正規表現の基本概念
正規表現の基本構成要素には、以下のようなものがあります。
- 文字クラス:
[abc]はa、b、またはcのいずれかを示します。 - 量指定子:
*（0回以上）、+（1回以上）、?（0回または1回）、{n,m}（n回からm回）。 - アンカー:
^（行頭）、$（行末）。 - 特殊文字:
.（任意の1文字）、\d（数字）、\w（単語文字）、\s（空白文字）。
SQLにおける正規表現の利用
SQLでは、データベースシステムに応じて正規表現を利用するための関数が提供されています。例えば、以下のような関数があります。
- MySQL:
REGEXPまたはRLIKE演算子。 - PostgreSQL:
SIMILAR TO演算子、~（マッチ）、~*（ケースインセンシティブマッチ）。 - Oracle:
REGEXP_LIKE関数。 - SQL Server: 2025（17.x）では REGEXP_LIKE を利用できます（互換性レベル170以上）。旧バージョンとは分けて確認します。
これらの関数を使用することで、SQLクエリ内で正規表現によるパターンマッチングが可能となります。例えば、MySQLでの使用例は以下の通りです。
SELECT * FROM users WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+[.][a-zA-Z]{2,}$';このクエリは、メールアドレスの形式に一致するレコードを検索します。
SQLの正規表現関数の紹介
SQLにおいて正規表現を利用するための関数は、データベースシステムによって異なります。ここでは、主要なデータベースシステムで利用可能な正規表現関数とその基本的な使い方を紹介します。
MySQLの正規表現関数
MySQLでは、REGEXPまたはRLIKE演算子を使用して正規表現によるパターンマッチングが可能です。
SELECT * FROM users WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+[.][a-zA-Z]{2,}$';このクエリは、メールアドレスの形式に一致するレコードを検索します。
PostgreSQLの正規表現関数
PostgreSQLでは、SIMILAR TO演算子や正規表現マッチ演算子（~、~*）を使用します。
SELECT * FROM users WHERE email ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+[.][a-zA-Z]{2,}$';このクエリもメールアドレス形式に一致するレコードを検索します。
Oracleの正規表現関数
Oracleでは、REGEXP_LIKE関数を使用して正規表現によるパターンマッチングを行います。
SELECT * FROM users WHERE REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+[.][a-zA-Z]{2,}$');このクエリも同様に、メールアドレス形式に一致するレコードを検索します。
SQL Serverの正規表現関数
SQL Server 2025（17.x）の REGEXP_LIKE は互換性レベル170以上が必要です。旧バージョンでは同じ関数が使えるとは限りません。CLRの有効化やDB互換性の変更を、この検索例のためだけに本番で行わないでください。
-- SQL Server 2025, compatibility level 170+
SELECT * FROM users WHERE REGEXP_LIKE(username, '^[a-z]+$', 'c');基本的な正規表現パターンの使用例
正規表現を使用することで、SQLクエリ内でのパターンマッチングが簡単かつ柔軟に行えます。ここでは、いくつかの基本的な正規表現パターンの使用例を紹介します。
特定の文字列を含む検索
特定の文字列を含むレコードを検索する場合、正規表現を使用することで柔軟な検索が可能です。以下は、name列に「john」という文字列を含むレコードを検索する例です。
SELECT * FROM users WHERE name REGEXP 'john';特定のパターンに一致する検索
特定のパターンに一致する文字列を検索することで、データを効果的にフィルタリングできます。例えば、phone列に数字3桁、ハイフン、数字3桁、ハイフン、数字4桁の形式を持つ電話番号を検索する場合は以下のようにします。
SELECT * FROM users WHERE phone REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';複数の条件に一致する検索
複数の条件に一致する文字列を検索する場合も、正規表現が役立ちます。例えば、email列に「gmail.com」または「yahoo.com」のドメインを持つメールアドレスを検索する場合は以下のようにします。
SELECT * FROM users WHERE email REGEXP '(@gmail[.]com|@yahoo[.]com)$';大文字と小文字を区別しない検索
大文字と小文字を区別せずに検索を行いたい場合、PostgreSQLでは正規表現マッチ演算子（~*）を使用します。
SELECT * FROM users WHERE name ~* 'john';開始文字や終了文字を指定した検索
特定の文字で始まる、または終わる文字列を検索することも可能です。例えば、username列が「a」で始まるレコードを検索する場合は以下のようにします。
SELECT * FROM users WHERE username REGEXP '^a';次に、username列が「z」で終わるレコードを検索する場合は以下のようにします。
SELECT * FROM users WHERE username REGEXP 'z$';これらの基本的な使用例を通じて、正規表現を活用したパターンマッチングがどのように行われるかを理解できます。
高度な正規表現パターンの使用例
基本的な正規表現パターンに加えて、より複雑なパターンマッチングやフィルタリングも可能です。ここでは、高度な正規表現の使用例を紹介します。
否定的なパターンのマッチング
特定のパターンに一致しない文字列を検索する場合、否定的なパターンマッチングを使用します。例えば、email列に「example.com」以外のドメインを持つメールアドレスを検索する場合は以下のようにします。
SELECT * FROM users WHERE email NOT REGEXP '@example[.]com$';繰り返しパターンのマッチング
特定の文字列が複数回繰り返されるパターンを検索する場合、繰り返しパターンを使用します。例えば、comments列に連続した3つ以上の数字が含まれるレコードを検索する場合は以下のようにします。
SELECT * FROM feedback WHERE comments REGEXP '[0-9]{3,}';条件付きパターンのマッチング
特定の条件に応じて異なるパターンをマッチングさせる場合、条件付きパターンを使用します。例えば、address列に「Street」または「St.」が含まれるレコードを検索する場合は以下のようにします。
SELECT * FROM locations WHERE address REGEXP 'Street|St[.]';キャプチャグループと後方参照
正規表現のキャプチャグループと後方参照を使用することで、より精密なパターンマッチングが可能になります。例えば、serial_number列に「AB12AB12」のように同じパターンが繰り返されるレコードを検索する場合は以下のようにします。
SELECT * FROM products WHERE serial_number REGEXP '^(..)(..)\\1\\2$';この例では、最初の2文字と次の2文字の繰り返しパターンをマッチングさせています。
非キャプチャグループ
非キャプチャグループを使用することで、グループ化はするが、後方参照では使用しないパターンを指定できます。例えば、description列に「color:」の後に続く色名が「red」、「green」、または「blue」であるレコードを検索する場合は以下のようにします。
SELECT * FROM items WHERE description REGEXP 'color:(?:red|green|blue)';複数行パターンのマッチング
複数行に渡るパターンマッチングを行う場合、特定のフラグを使用します。PostgreSQLで各行の先頭・末尾を認識させ、pattern だけの行が含まれるかを調べる例です。~ 演算子に ESCAPE 句は付けません。
SELECT * FROM documents WHERE content ~ '(?n)^pattern$';これらの高度な正規表現パターンを使用することで、SQLクエリにおけるパターンマッチングとフィルタリングがさらに強力になります。
パフォーマンスと最適化
正規表現を使用したSQLクエリは非常に強力ですが、パフォーマンスへの影響に注意が必要です。ここでは、正規表現を用いたクエリのパフォーマンスを最適化するためのアプローチを紹介します。
インデックスの活用
REGEXP を使うだけで通常のB-treeインデックスが有効になるとは限りません。単なる前方一致なら LIKE の候補も比較し、実行計画を確認します。DB・照合順序・データ分布によって結果が変わるため、下の例も高速化を保証しません。
-- MySQL: 実行計画を比較する読み取り例
EXPLAIN SELECT * FROM users WHERE username REGEXP '^abc';
EXPLAIN SELECT * FROM users WHERE username LIKE 'abc%';部分一致の回避
特に複雑な正規表現パターンは、パフォーマンスに大きな影響を与えることがあります。可能な限り、正規表現の使用を限定し、部分一致を回避することでパフォーマンスを向上させます。
-- 複雑なパターンの例（パフォーマンスが低下する可能性あり）
SELECT * FROM users WHERE email REGEXP '.*@example[.](com|net|org)$';
-- LIKE に置き換えた比較候補（高速化は実測で確認）
SELECT * FROM users WHERE email LIKE '%@example.com' OR email LIKE '%@example.net' OR email LIKE '%@example.org';クエリの最適化
正規表現を使用するクエリを最適化するための一般的な方法には、クエリを分割して、正規表現の評価を必要最小限に抑えることがあります。例えば、大量のデータセットに対して正規表現を使用する前に、先にフィルタリングを行うことが有効です。
-- 大量のデータに対して正規表現を適用する非効率なクエリ
SELECT * FROM logs WHERE message REGEXP 'error[0-9]{3}';
-- 追加条件が検索要件に合う場合の候補（同じ結果とは限らない）
SELECT * FROM logs WHERE severity = 'ERROR' AND message REGEXP 'error[0-9]{3}';正規表現エンジンの特性を理解する
データベースごとに正規表現エンジンの特性が異なるため、それぞれの特性を理解しておくことが重要です。例えば、MySQL 8.0/8.4の大文字・小文字の扱いは文字セット・照合順序と match_type に依存します。REGEXP_LIKE の第3引数 c / i で指定できます。一方、PostgreSQLでは~*を使用してケースインセンシティブなマッチングができます。正規表現エンジンの違いを理解し、適切にクエリを調整することでパフォーマンスを向上させることができます。
追加条件で必要な行を除外していないか確認し、実行計画と同条件の計測で比較してください。WHERE の記述順が評価順を保証するわけではありません。
ケーススタディ
ここでは、実際のビジネスシナリオにおいて正規表現を用いたSQLクエリの使用例を紹介します。これにより、正規表現を使ったパターンマッチングとフィルタリングの実践的な応用を理解できます。
メールアドレスの検証
メールアドレスのフォーマットが正しいかを検証するために正規表現を使用します。この例では、ユーザーのメールアドレスが正しい形式かどうかをチェックします。
SELECT user_id, email FROM users WHERE email NOT REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+[.][a-zA-Z]{2,}$';簡易パターンに合わない候補を抽出します。RFCに沿う全メール形式やメールの実在を判定する式ではなく、NULLも別途確認が必要です。
電話番号のフォーマット統一
まず更新せず、変換結果を SELECT で確認します。次は MySQL 8.0/8.4 で「123-456-7890」または「123 456 7890」を対象にした限定例です。国番号・内線・日本の桁数の異なる番号には適用しません。
SELECT phone,
REGEXP_REPLACE(phone, '^([0-9]{3})[- ]([0-9]{3})[- ]([0-9]{4})$', '$1-$2-$3') AS normalized
FROM contacts;パターンに一致しない値はそのままです。全電話番号が正しく統一されるわけではありません。本番更新は対象行・バックアップ・ロールバック方法を確認して別途設計します。
ログデータの解析
ログデータから特定のエラーメッセージを抽出するために正規表現を使用します。この例では、エラーログから特定のエラーパターンを含むメッセージを抽出します。
SELECT log_id, message FROM logs WHERE message REGEXP 'ERROR [0-9]{3}:';このクエリは、エラーメッセージに「ERROR 123:」のようなパターンを含むログエントリを抽出します。
ユーザー入力データのクレンジング
ユーザーが入力したデータに余分な空白や特定の記号が含まれている場合、それをクレンジングします。例えば、名前フィールドから余分な空白を取り除きます。
SELECT name, REGEXP_REPLACE(name, '[[:space:]]+', ' ') AS normalized_name FROM users;MySQL 8.0/8.4 の例です。置換後の候補を表示するだけで、元データは変更しません。空白に見えても名前として必要な文字があるため、確認なく上書きしないでください。
製品コードのフォーマットチェック
特定のフォーマットに一致する製品コードを検索します。この例では、製品コードが「ABC-1234」の形式であるかどうかをチェックします。
SELECT product_id, product_code FROM products WHERE product_code REGEXP '^[A-Z]{3}-[0-9]{4}$';このクエリは、正しいフォーマットの製品コードを持つレコードを抽出します。
データベースのクリーニング
正規表現は確認候補の抽出に使います。単語の部分一致だけでは文脈を判定できないため、次の例は削除ではなくレビュー対象の SELECT です。
SELECT * FROM comments WHERE comment_text REGEXP '(badword1|badword2|badword3)';このクエリでは元データは変更されません。誤検出を確認してから、運用上必要な対応を判断してください。
これらのケーススタディを通じて、正規表現を用いたSQLクエリの実践的な応用方法を理解し、ビジネスシナリオでの活用に役立ててください。
まとめ
正規表現を用いたSQLクエリは、柔軟で強力なパターンマッチングとフィルタリングを可能にします。本記事では、正規表現の基本概念から、主要なデータベースシステムでの利用方法、基本的および高度なパターンの使用例、パフォーマンスの最適化手法、そして実際のビジネスシナリオでの応用例を詳しく解説しました。
正規表現を活用することで、複雑なデータ検索やクレンジングが容易になり、データベース操作の効率と精度を向上させることができます。特に、大規模なデータセットに対しても効果的にデータ操作を行うためには、正規表現のパフォーマンスと最適化を考慮することが重要です。
正規表現の力を最大限に活用することで、SQLクエリの可能性を広げ、ビジネスにおけるデータ活用の幅を広げることができるでしょう。これからも正規表現を活用して、効率的かつ正確なデータベース操作を行ってください。
公式資料：MySQL 正規表現 / PostgreSQL パターンマッチ / SQL Server REGEXP_LIKE。
コメント