SQLの正規表現関数完全ガイド｜データベース別の使い方と実例【2025年版】

データベース・SQL

「メールアドレスの形式をチェックしたい」
「文字列から特定のパターンを抽出したい」
「データの中から不要な文字を削除したい」

そんなときに強力な味方となるのが、SQLの正規表現（Regex）機能です。

文字列の検索、抽出、置換、検証など、様々な文字列処理を効率的に行えます。

この記事では、主要なデータベースシステムでの正規表現機能について、基本的な使い方から実践的な応用例まで、わかりやすく解説します。

スポンサーリンク

SQLの正規表現とは

基本的な概念

正規表現（Regular Expression、Regex）は、文字列のパターンを表現するための記法です。

SQLにおいても、文字列の検索や操作において正規表現を活用できます。

どんなときに使うの？

データの検証

  • メールアドレスの形式チェック
  • 電話番号の正当性確認
  • 郵便番号のパターン検証

データの抽出

  • URLからドメイン名を取得
  • 文章から日付を抽出
  • ログファイルから特定の情報を取得

データクレンジング

  • 不要な文字や記号の削除
  • 文字列の正規化
  • フォーマットの統一

パターンマッチング

  • 特定の条件を満たすデータの検索
  • あいまい検索の実現
  • 複雑な検索条件の指定

データベース別の正規表現機能

MySQL / MariaDB

MySQLでは豊富な正規表現関数が利用できます。

基本的な関数

REGEXP / RLIKE（パターンマッチング判定）

-- 基本的な使い方
SELECT * FROM users 
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

REGEXP_LIKE（推奨方式）

-- MySQL 8.0以降で推奨
SELECT name, email
FROM users 
WHERE REGEXP_LIKE(email, '^[^@]+@[^@]+\.[^@]+$');

抽出・操作関数

REGEXP_SUBSTR（文字列抽出）

-- URLからドメイン名を抽出
SELECT 
    url,
    REGEXP_SUBSTR(url, 'https?://([^/]+)', 1, 1, '', 1) AS domain
FROM websites;

REGEXP_REPLACE（置換）

-- 電話番号のハイフンを削除
SELECT 
    phone_number,
    REGEXP_REPLACE(phone_number, '[^0-9]', '') AS clean_phone
FROM contacts;

REGEXP_INSTR（位置取得）

-- 最初の数字の位置を取得
SELECT 
    text_data,
    REGEXP_INSTR(text_data, '[0-9]') AS first_digit_position
FROM sample_data;

REGEXP_COUNT（マッチ回数）

-- 文字列内の単語数をカウント
SELECT 
    content,
    REGEXP_COUNT(content, '\\b\\w+\\b') AS word_count
FROM articles;

MySQL実用例

-- サンプルデータ
CREATE TABLE user_data (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    phone VARCHAR(20),
    website VARCHAR(255)
);

INSERT INTO user_data VALUES
    (1, '田中太郎', 'tanaka@example.com', '090-1234-5678', 'https://tanaka-blog.com/about'),
    (2, '山田花子', 'yamada.invalid-email', '03-9876-5432', 'http://yamada.net'),
    (3, '佐藤次郎', 'sato@company.co.jp', '080.1111.2222', 'https://sato-portfolio.org/profile'),
    (4, '鈴木三郎', 'suzuki@test.com', '070-5555-6666', 'ftp://files.suzuki.com');

-- 正規表現を使った複合的なデータ処理
SELECT 
    name,
    email,
    CASE 
        WHEN REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') 
        THEN '有効'
        ELSE '無効'
    END AS email_status,
    REGEXP_REPLACE(phone, '[^0-9]', '') AS clean_phone,
    REGEXP_SUBSTR(website, 'https?://([^/]+)', 1, 1, '', 1) AS domain
FROM user_data;

PostgreSQL

PostgreSQLは独特な演算子記法とPOSIX準拠の正規表現をサポートしています。

基本的な演算子

パターンマッチング演算子

-- 基本的なマッチング
SELECT * FROM products 
WHERE product_code ~ '^PRD-[0-9]{4}$';

-- 大文字小文字を無視
SELECT * FROM customers 
WHERE name ~* 'yamada|tanaka';

-- マッチしない場合
SELECT * FROM orders 
WHERE order_status !~ 'cancelled|pending';

関数による操作

regexp_match（最初の一致）

-- 文字列から最初のパターンを抽出
SELECT 
    log_entry,
    regexp_match(log_entry, '(\d{4}-\d{2}-\d{2})') AS date_match
FROM access_logs;

regexp_matches（全ての一致）

-- すべてのマッチを取得
SELECT 
    text_content,
    regexp_matches(text_content, '\b\w+@\w+\.\w+\b', 'g') AS email_matches
FROM documents;

regexp_replace（置換）

-- 複数の空白を1つにまとめる
SELECT 
    description,
    regexp_replace(description, '\s+', ' ', 'g') AS cleaned_description
FROM products;

regexp_split_to_table（分割）

-- 文字列を分割してテーブルとして取得
SELECT regexp_split_to_table('apple,banana,orange', ',') AS fruit;

PostgreSQL実用例

-- ログ解析の例
CREATE TABLE access_logs (
    id SERIAL PRIMARY KEY,
    log_entry TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO access_logs (log_entry) VALUES
    ('2025-01-15 10:30:45 GET /api/users/123 200 0.045s user@example.com'),
    ('2025-01-15 10:31:12 POST /login 401 0.023s invalid@test.com'),
    ('2025-01-15 10:32:00 GET /dashboard 200 0.156s admin@company.co.jp');

-- ログからの情報抽出
SELECT 
    id,
    (regexp_match(log_entry, '(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})'))[1] AS timestamp,
    (regexp_match(log_entry, '(GET|POST|PUT|DELETE)'))[1] AS method,
    (regexp_match(log_entry, '(GET|POST|PUT|DELETE)\s+([^\s]+)'))[2] AS endpoint,
    (regexp_match(log_entry, '(\d{3})\s+'))[1] AS status_code,
    (regexp_match(log_entry, '([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,})'))[1] AS email
FROM access_logs;

Oracle

Oracleは包括的な正規表現関数セットを提供しています。

基本的な関数

REGEXP_LIKE（パターンマッチング）

-- 基本的なパターンマッチング
SELECT employee_id, first_name, last_name
FROM employees 
WHERE REGEXP_LIKE(first_name, '^[AE].*');

REGEXP_SUBSTR（部分文字列抽出）

-- 文字列から数値部分を抽出
SELECT 
    product_code,
    REGEXP_SUBSTR(product_code, '[0-9]+') AS numeric_part
FROM products;

REGEXP_REPLACE（置換）

-- 複数のパターンを一度に置換
SELECT 
    description,
    REGEXP_REPLACE(description, '[[:punct:]]', '') AS no_punctuation
FROM items;

REGEXP_INSTR（位置検索）

-- 特定パターンの位置を検索
SELECT 
    text_data,
    REGEXP_INSTR(text_data, '[0-9]{4}') AS year_position
FROM documents;

REGEXP_COUNT（マッチ数）

-- パターンのマッチ数をカウント
SELECT 
    content,
    REGEXP_COUNT(content, '\b[A-Z][a-z]+\b') AS capitalized_words
FROM articles;

Oracle実用例

-- 顧客データの正規化
CREATE TABLE customer_raw (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    phone VARCHAR2(30),
    email VARCHAR2(255),
    address VARCHAR2(500)
);

INSERT INTO customer_raw VALUES
    (1, '田中  太郎', '090-1234-5678', 'TANAKA@EXAMPLE.COM', '東京都新宿区1-2-3'),
    (2, '山田　花子', '03.9876.5432', 'yamada@test.co.jp', '大阪府大阪市北区4-5-6'),
    (3, '佐藤 次郎', '080 1111 2222', 'sato@company.com', '愛知県名古屋市中区7-8-9');

-- 正規表現を使ったデータクレンジング
SELECT 
    id,
    REGEXP_REPLACE(name, '\s+', ' ') AS clean_name,
    REGEXP_REPLACE(phone, '[^0-9]', '') AS clean_phone,
    LOWER(email) AS clean_email,
    REGEXP_REPLACE(address, '([都道府県])([市区町村])', '\1 \2') AS formatted_address
FROM customer_raw;

SQL Server (2022以降)

SQL Serverは最新バージョンで正規表現機能を大幅に強化しました。

基本的な関数

STRING_SPLIT（文字列分割）

-- 区切り文字での分割
SELECT value 
FROM STRING_SPLIT('apple,banana,orange', ',');

REGEXP_LIKE（パターンマッチング）

-- SQL Server 2022以降
SELECT customer_id, email
FROM customers 
WHERE REGEXP_LIKE(email, '^[^@]+@[^@]+\.[^@]+$');

SQL Server実用例

-- 従来の方法と正規表現の比較
CREATE TABLE sample_data (
    id INT IDENTITY(1,1) PRIMARY KEY,
    text_field NVARCHAR(500)
);

INSERT INTO sample_data (text_field) VALUES
    ('Order #12345 shipped to tokyo@example.com'),
    ('Invoice INV-67890 sent to osaka@test.com'),
    ('Product PRD_11111 reviewed by user@company.co.jp');

-- 従来の方法（LIKE、CHARINDEX、SUBSTRING等）
SELECT 
    id,
    text_field,
    CASE 
        WHEN text_field LIKE '%@%.%' THEN 'Contains Email'
        ELSE 'No Email'
    END AS email_check_traditional
FROM sample_data;

-- 正規表現を使用（SQL Server 2022以降）
-- 注意：この例は将来の機能を想定しています
/*
SELECT 
    id,
    text_field,
    CASE 
        WHEN REGEXP_LIKE(text_field, '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}') 
        THEN 'Contains Email'
        ELSE 'No Email'
    END AS email_check_regex
FROM sample_data;
*/

SQLite

SQLiteは限定的ながら正規表現機能をサポートしています。

基本的な機能

REGEXP演算子（拡張機能が必要）

-- SQLite拡張を有効にした場合
SELECT * FROM users 
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

代替的な方法

-- GLOBパターンマッチング（ワイルドカード）
SELECT * FROM products 
WHERE product_code GLOB 'PRD-[0-9][0-9][0-9][0-9]';

-- LIKEパターンマッチング
SELECT * FROM customers 
WHERE name LIKE '%田中%' OR name LIKE '%山田%';

実践的な活用例

データ検証パターン

メールアドレスの検証

-- 基本的なメールアドレス検証
SELECT 
    email,
    CASE 
        WHEN REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') 
        THEN '有効'
        ELSE '無効'
    END AS validation_result
FROM user_emails;

-- より厳密な検証
SELECT 
    email,
    CASE 
        WHEN REGEXP_LIKE(email, '^[a-zA-Z0-9][a-zA-Z0-9._%+-]*[a-zA-Z0-9]@[a-zA-Z0-9][a-zA-Z0-9.-]*[a-zA-Z0-9]\.[a-zA-Z]{2,}$') 
        THEN '有効'
        ELSE '無効'
    END AS strict_validation
FROM user_emails;

電話番号の検証

-- 日本の電話番号パターン
SELECT 
    phone_number,
    CASE 
        WHEN REGEXP_LIKE(phone_number, '^0[0-9]{1,4}-[0-9]{1,4}-[0-9]{3,4}$') THEN '固定電話'
        WHEN REGEXP_LIKE(phone_number, '^0[789]0-[0-9]{4}-[0-9]{4}$') THEN '携帯電話'
        WHEN REGEXP_LIKE(phone_number, '^050-[0-9]{4}-[0-9]{4}$') THEN 'IP電話'
        ELSE '不明な形式'
    END AS phone_type
FROM contact_info;

データ抽出パターン

URLからの情報抽出

-- URLの各部分を抽出
SELECT 
    url,
    REGEXP_SUBSTR(url, '^https?://') AS protocol,
    REGEXP_SUBSTR(url, 'https?://([^/]+)', 1, 1, '', 1) AS domain,
    REGEXP_SUBSTR(url, 'https?://[^/]+(/.*)?$', 1, 1, '', 1) AS path
FROM website_urls;

日付・時刻の抽出

-- 様々な日付形式を抽出
SELECT 
    text_content,
    REGEXP_SUBSTR(text_content, '[0-9]{4}-[0-9]{2}-[0-9]{2}') AS iso_date,
    REGEXP_SUBSTR(text_content, '[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}') AS us_date,
    REGEXP_SUBSTR(text_content, '[0-9]{4}年[0-9]{1,2}月[0-9]{1,2}日') AS jp_date
FROM documents;

データクレンジングパターン

文字列の正規化

-- 余分な空白の削除と正規化
SELECT 
    original_text,
    REGEXP_REPLACE(
        REGEXP_REPLACE(original_text, '^\s+|\s+$', ''), 
        '\s+', ' '
    ) AS normalized_text
FROM text_data;

特殊文字の処理

-- HTMLタグの除去
SELECT 
    html_content,
    REGEXP_REPLACE(html_content, '<[^>]+>', '') AS plain_text
FROM web_content;

-- 数字以外の文字を削除
SELECT 
    mixed_content,
    REGEXP_REPLACE(mixed_content, '[^0-9]', '') AS numbers_only
FROM mixed_data;

正規表現のパフォーマンス最適化

インデックスの活用

正規表現はインデックスを効果的に使用できない場合があります。

-- 悪い例：正規表現のみ
SELECT * FROM products 
WHERE REGEXP_LIKE(product_code, '^PRD-.*');

-- 良い例：範囲指定との組み合わせ
SELECT * FROM products 
WHERE product_code >= 'PRD-' 
  AND product_code < 'PRE-'
  AND REGEXP_LIKE(product_code, '^PRD-[0-9]{4}$');

事前フィルタリング

-- 事前に簡単な条件でフィルタリング
SELECT * FROM large_table 
WHERE category = 'electronics'  -- インデックスが効く条件
  AND REGEXP_LIKE(product_name, '(iPhone|Galaxy|Pixel)');

注意点とベストプラクティス

パフォーマンスへの配慮

大量データでの使用は注意

  • 正規表現は計算コストが高い
  • 可能な限り事前フィルタリングを行う
  • インデックスと組み合わせて使用

可読性の維持

複雑なパターンは説明を追加

-- 複雑な正規表現にはコメントを追加
SELECT 
    email,
    REGEXP_LIKE(
        email, 
        '^[a-zA-Z0-9][a-zA-Z0-9._%+-]*[a-zA-Z0-9]@[a-zA-Z0-9][a-zA-Z0-9.-]*[a-zA-Z0-9]\.[a-zA-Z]{2,}$'
        /* 
         * メールアドレスの検証パターン:
         * - 最初と最後は英数字
         * - @マークで区切られた2つの部分
         * - ドメイン部分は最低2文字のTLD
         */
    ) AS is_valid_email
FROM users;

データベース間の互換性

各データベースで正規表現の記法が微妙に異なるため、移植時は注意が必要です。

-- MySQL/Oracle形式
REGEXP_LIKE(column, pattern)

-- PostgreSQL形式  
column ~ pattern

-- 互換性を考慮した書き方（関数で統一）
-- または、各環境に応じて書き換え

まとめ

SQLの正規表現機能は、文字列処理において非常に強力なツールです。

この記事のポイント

データベース別の特徴

  • MySQL：豊富な関数セット（REGEXP_LIKE、REGEXP_SUBSTR等）
  • PostgreSQL：独特の演算子記法（~、~*）とPOSIX準拠
  • Oracle：包括的な関数群と高度なオプション
  • SQL Server：最新版で機能強化、従来は限定的

主要な用途

  • データ検証：メール、電話番号、ID形式のチェック
  • データ抽出：URL、日付、特定パターンの取得
  • データクレンジング：不要文字の削除、形式統一
  • パターンマッチング：複雑な検索条件の実現

実装のコツ

  • パフォーマンスを考慮した事前フィルタリング
  • 複雑なパターンには説明コメントを追加
  • データベース間の互換性に注意
  • テストデータでの十分な検証

Youtube

神話・歴史・伝承の解説チャンネル

スポンサーリンク

コメント

タイトルとURLをコピーしました