【Oracle】正規表現（REGEXP）関数の使い方完全ガイド｜REGEXP_LIKE・REGEXP_REPLACE・REGEXP_SUBSTR・REGEXP_INSTR・REGEXP_COUNT

-- 数字のみで構成される行を抽出
SELECT employee_id, phone_number
FROM   employees
WHERE  REGEXP_LIKE(phone_number, '^[0-9]+$');

-- アルファベットで始まる名前（大文字小文字不問）
SELECT first_name
FROM   employees
WHERE  REGEXP_LIKE(first_name, '^[a-z]', 'i');  -- 'i' = 大文字小文字を区別しない

-- ひらがな・カタカナを含む行を除外
SELECT customer_name
FROM   customers
WHERE  NOT REGEXP_LIKE(customer_name, '[[:alpha:]]');

-- メールアドレス形式のCHECK制約
ALTER TABLE users
    ADD CONSTRAINT chk_email_format
    CHECK (REGEXP_LIKE(email, '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$'));

-- 郵便番号（7桁数字またはハイフンあり形式）のCHECK制約
ALTER TABLE addresses
    ADD CONSTRAINT chk_zip_format
    CHECK (REGEXP_LIKE(zip_code, '^[0-9]{3}-?[0-9]{4}$'));

-- 数字以外の文字を全て除去（電話番号のハイフン・括弧を取り除く）
SELECT
    phone_raw,
    REGEXP_REPLACE(phone_raw, '[^0-9]', '') AS phone_digits_only
FROM (
    SELECT '090-1234-5678' AS phone_raw FROM dual UNION ALL
    SELECT '(03)1234-5678' FROM dual UNION ALL
    SELECT '0312345678'    FROM dual
);
-- 結果: 09012345678 / 0312345678 / 0312345678

-- 連続する空白を1つにまとめる
SELECT REGEXP_REPLACE('Hello   World   !', '\s+', ' ') AS normalized
FROM   dual;
-- 結果: 'Hello World !'

-- HTMLタグを除去する
SELECT REGEXP_REPLACE(
    '太郎さんへ',
    '<[^>]+>',   -- < から始まり > で終わるHTMLタグにマッチ
    ''           -- 空文字で置換（削除）
) AS plain_text
FROM   dual;
-- 結果: '太郎さんへ'

-- 先頭・末尾の空白を削除（TRIMと同等だが正規表現版）
SELECT REGEXP_REPLACE('  hello world  ', '^\s+|\s+$', '') AS trimmed
FROM   dual;
-- 結果: 'hello world'

-- 後方参照を使って日付形式を変換（YYYYMMDD → YYYY/MM/DD）
SELECT
    REGEXP_REPLACE('20260328', '^([0-9]{4})([0-9]{2})([0-9]{2})$', '
//')
        AS formatted_date
FROM   dual;
-- 結果: '2026/03/28'

-- 氏名の「姓名」順を「名姓」に入れ替え
SELECT
    REGEXP_REPLACE('Yamada Taro', '^(\S+)\s+(\S+)$', ' 
') AS swapped
FROM   dual;
-- 結果: 'Taro Yamada'

-- 文字列から最初の数値を抽出
SELECT
    REGEXP_SUBSTR('Order #12345 shipped', '[0-9]+') AS order_num
FROM   dual;
-- 結果: '12345'

-- 2番目のマッチを取得（occurrence=2）
SELECT
    REGEXP_SUBSTR('abc123def456ghi', '[0-9]+', 1, 2) AS second_num
FROM   dual;
-- 結果: '456'

-- ドメイン名だけを抽出（@以降の部分）
SELECT
    REGEXP_SUBSTR(email, '@(.+)$', 1, 1, 'i', 1) AS domain
FROM   users;
-- subexpr=1 で1番目のキャプチャグループを返す

-- CSV形式の文字列から各要素を取り出す（occurrence を変えながらSELECT）
SELECT
    REGEXP_SUBSTR('apple,banana,cherry,date', '[^,]+', 1, 1) AS item1,
    REGEXP_SUBSTR('apple,banana,cherry,date', '[^,]+', 1, 2) AS item2,
    REGEXP_SUBSTR('apple,banana,cherry,date', '[^,]+', 1, 3) AS item3,
    REGEXP_SUBSTR('apple,banana,cherry,date', '[^,]+', 1, 4) AS item4
FROM   dual;
-- 結果: apple / banana / cherry / date

SELECT
    email,
    REGEXP_SUBSTR(email, '^[^@]+')        AS local_part,   -- @より前
    REGEXP_SUBSTR(email, '[^@]+$')        AS domain_part   -- @より後
FROM   users
WHERE  REGEXP_LIKE(email, '^[^@]+@[^@]+\.[^@]+$');  -- 有効なメール形式のみ

-- 最初の数字の開始位置を取得
SELECT
    REGEXP_INSTR('Price: 1980 yen', '[0-9]+') AS pos
FROM   dual;
-- 結果: 8 （'1'の位置）

-- マッチ終了の次の位置を取得（return_opt=1）
SELECT
    REGEXP_INSTR('Price: 1980 yen', '[0-9]+', 1, 1, 1) AS pos_after
FROM   dual;
-- 結果: 12 （'0'の次）

-- IPアドレスの第2オクテットの開始位置
SELECT
    REGEXP_INSTR('192.168.0.1', '[0-9]+', 1, 2) AS second_octet_pos
FROM   dual;
-- 結果: 5 （'168'の開始位置）

-- 特定パターンが存在するかどうかの確認（0 = 存在しない）
SELECT
    CASE
        WHEN REGEXP_INSTR(description, '<script', 1, 1, 0, 'i') > 0
        THEN 'XSS疑い'
        ELSE 'OK'
    END AS security_check
FROM   articles;

-- 文字列中の数字の塊の数を数える
SELECT
    REGEXP_COUNT('abc123def456ghi789', '[0-9]+') AS num_groups
FROM   dual;
-- 結果: 3

-- カンマの数（要素数 - 1）を数える
SELECT
    product_code,
    REGEXP_COUNT(tags, ',') + 1 AS tag_count
FROM   products;

-- 特定の単語が何回登場するか
SELECT
    REGEXP_COUNT(description, 'Oracle', 1, 'i') AS oracle_mentions
FROM   articles;
--  は単語境界（単語の境界を示すアサーション）

-- 正規表現によるメールアドレスバリデーション
SELECT
    email,
    CASE
        WHEN REGEXP_LIKE(email,
             '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$')
        THEN '有効'
        ELSE '無効'
    END AS email_status
FROM   users;

-- ドメインだけを一覧で集計
SELECT
    REGEXP_SUBSTR(email, '@(.+)$', 1, 1, NULL, 1) AS domain,
    COUNT(*) AS user_count
FROM   users
WHERE  REGEXP_LIKE(email, '@')
GROUP BY REGEXP_SUBSTR(email, '@(.+)$', 1, 1, NULL, 1)
ORDER BY user_count DESC;

-- 様々な形式の電話番号を数字のみにする
SELECT
    phone_raw,
    REGEXP_REPLACE(phone_raw, '[^0-9]', '') AS digits_only
FROM (
    SELECT '090-1234-5678' AS phone_raw FROM dual UNION ALL
    SELECT '(090)1234 5678'             FROM dual UNION ALL
    SELECT '090.1234.5678'              FROM dual UNION ALL
    SELECT '09012345678'                FROM dual
);

-- ハイフン区切り形式に統一（11桁の携帯番号）
SELECT
    REGEXP_REPLACE(
        REGEXP_REPLACE(phone_raw, '[^0-9]', ''),   -- まず数字だけにする
        '^([0-9]{3})([0-9]{4})([0-9]{4})$',        -- 3-4-4 に分割
        '
--'
    ) AS formatted_phone
FROM   customers;

-- 〒123-4567 / 1234567 どちらも許可するバリデーション
SELECT
    zip_code,
    CASE
        WHEN REGEXP_LIKE(zip_code, '^[0-9]{3}-?[0-9]{4}$')
        THEN '正常'
        ELSE '不正'
    END AS zip_status
FROM   addresses;

-- ハイフンなしに統一する
SELECT
    REGEXP_REPLACE(zip_code, '-', '') AS zip_normalized
FROM   addresses
WHERE  REGEXP_LIKE(zip_code, '^[0-9]{3}-?[0-9]{4}$');

-- HTMLタグを全て除去してプレーンテキストに変換
SELECT
    REGEXP_REPLACE(html_content, '<[^>]+>', '') AS plain_text
FROM   articles;

-- 制御文字（タブ・改行・復帰）を除去
SELECT
    REGEXP_REPLACE(raw_text, '[[:cntrl:]]', '') AS clean_text
FROM   import_data;

-- 英数字・スペース以外の文字を除去（記号をすべて削除）
SELECT
    REGEXP_REPLACE(user_input, '[^[:alnum:] ]', '') AS safe_text
FROM   user_comments;

-- アクセスログ例: "192.168.1.100 - [28/Mar/2026:10:30:00] GET /index.html 200"
SELECT
    log_line,
    REGEXP_SUBSTR(log_line, '^[0-9.]+')                        AS ip_address,
    REGEXP_SUBSTR(log_line, '\[([^\]]+)\]', 1, 1, NULL, 1)    AS access_time,
    REGEXP_SUBSTR(log_line, '(GET|POST|PUT|DELETE) \S+', 1, 1) AS method_path,
    REGEXP_SUBSTR(log_line, ' ([0-9]{3})$', 1, 1, NULL, 1)    AS status_code
FROM   access_logs;