【SQL】REPLACE関数完全ガイド｜文字列置換・削除・ネスト・REGEXP_REPLACE・実務パターンまで解説

2024.05.07

SQLのREPLACE関数は、文字列の中から指定した部分文字列を別の文字列に置き換えます。単純な置換だけでなく、削除（空文字に置換）・複数箇所の一括置換（ネスト）・UPDATE文でのデータ一括修正・マスキングや正規化など、実務で幅広く活躍します。

本記事では基本構文から、大文字/小文字の扱い・NULLの動作・正規表現版REGEXP_REPLACEとの使い分け・Oracle独自のTRANSLATE関数との違いまで、現場で使える知識を網羅します。

この記事で学べること：REPLACE関数の構文と動作・ネスト（複数置換）・削除への応用・UPDATE文での一括修正・大文字/小文字の扱いとDB別の違い・NULL挙動・REGEXP_REPLACEとTRANSLATEとの使い分け・電話番号正規化・マスキング・URL置換などの実務パターン集
スポンサーリンク

基本構文と動作

REPLACE関数は3つの引数を取ります。マッチしたすべての箇所を置換します（一か所だけ替える機能はありません）。

REPLACE基本構文
REPLACE(対象文字列, 検索文字列, 置換文字列)
引数 説明 NULL時の動作
対象文字列 置換する元の文字列またはカラム NULLを返す
検索文字列 見つけたい部分文字列 NULLを返す
置換文字列 置換後の文字列。空文字 '' にすると削除になる NULLを返す
基本の使い方
-- 基本: 文字列を置換
SELECT REPLACE('Hello, world!', 'world', 'SQL');
-- 結果: 'Hello, SQL!'

-- カラムに適用
SELECT REPLACE(email, '@old-domain.com', '@new-domain.com') AS new_email
FROM   users;

-- すべての出現箇所を置換（グローバル置換）
SELECT REPLACE('a-b-c-d', '-', '/');
-- 結果: 'a/b/c/d'  ← ハイフンを全部スラッシュに

-- 削除（第3引数を空文字にする）
SELECT REPLACE('Hello, world!', ',', '');
-- 結果: 'Hello world!'  ← カンマを削除
REPLACE は全件置換：検索文字列に一致したすべての箇所が置換されます。先頭の1件だけ替えたい場合はSUBSTRやREGEXP_REPLACEで対応してください。

大文字/小文字の扱い（よくある誤解）

「REPLACEは大文字/小文字を区別しない」という誤解が広まっていますが、実際はテーブル・カラムの照合順序（COLLATION）に従います。多くの環境では大文字/小文字を区別します。

大文字/小文字の確認
-- MySQL（utf8mb4_general_ci など CI collation の場合）: 区別しない
SELECT REPLACE('Hello World', 'hello', 'HI');
-- 環境によって: 'HI World' または 'Hello World'（collation依存）

-- 確実に大文字/小文字を区別する（MySQL: BINARY指定）
SELECT REPLACE(BINARY 'Hello World', 'hello', 'HI');
-- 結果: 'Hello World'  ← 小文字'hello'は見つからない

-- 大文字/小文字を無視して置換したい場合（MySQL）
SELECT REPLACE(LOWER('Hello World'), 'hello', 'hi');
-- 結果: 'hi world'  ← 先に LOWER してから置換

-- PostgreSQL: デフォルトで大文字/小文字を区別する
SELECT REPLACE('Hello World', 'hello', 'HI');
-- 結果: 'Hello World'  ← 'hello' は見つからない

-- Oracle / SQL Server: デフォルトで大文字/小文字を区別する
SELECT REPLACE('Hello World', 'hello', 'HI') FROM dual;
-- 結果: 'Hello World'
大文字/小文字を無視して置換したい場合：MySQL・PostgreSQL・Oracle・SQL Serverいずれも、大文字/小文字を無視したREPLACEは標準では提供されていません。回避策は①先にLOWER()してから置換する、②REGEXP_REPLACEでiフラグ（大文字小文字無視）を使う、の2択です。

NULLの扱い

3つの引数のうちいずれかがNULLの場合、結果はNULLになります。NULLが混入する可能性があるカラムにはCOALESCEで事前に空文字に変換することを推奨します。

NULL時の動作と対策
-- NULLが含まれると結果はNULL
SELECT REPLACE(NULL, '-', '');        -- NULL
SELECT REPLACE('a-b', NULL, '');      -- NULL
SELECT REPLACE('a-b', '-', NULL);     -- NULL

-- 対策: COALESCE でNULLを空文字に変換
SELECT REPLACE(COALESCE(phone, ''), '-', '') AS normalized_phone
FROM   customers;

-- カラムにNULLが入っている行はそのままNULLを保持
SELECT REPLACE(COALESCE(name, ''), ' ', '')  -- name がNULLなら '' → '' を返す
FROM   users;
NULLとREPLACEの注意：REPLACE対象のカラムにNULLが入っていると結果がNULLになります。NULLを空文字として扱いたいならCOALESCE(col, '')をネストしてください。NULLのままにしておきたい場合はCASE WHEN col IS NULL THEN NULL ELSE REPLACE(col, ...) ENDを使います。

ネストで複数パターンを一括置換する

1回のREPLACEで置換できるのは1種類のパターンだけです。複数パターンを置換したい場合はREPLACEをネスト（入れ子）にします。内側から外側へ順番に処理されます。

ネストREPLACE（複数パターン置換）
-- 電話番号から「-」「(」「)」「 」を除去して数字のみに
SELECT REPLACE(
    REPLACE(
        REPLACE(
            REPLACE(phone, '-', ''),
        '(', ''),
    ')', ''),
' ', '')
AS digits_only
FROM customers;

-- 例: '(03) 1234-5678' → '0312345678'

-- HTMLタグを除去（簡易版: <br>と<p>のみ）
SELECT REPLACE(REPLACE(description, '<br>', ' '), '<p>', '')
AS plain_text
FROM articles;

-- ドメイン変更（旧URL→新URL）
SELECT REPLACE(
    REPLACE(url, 'http://', 'https://'),
'old-domain.com', 'new-domain.com')
AS new_url
FROM pages;
ネストが深くなりすぎる場合はREGEXP_REPLACEを検討：4つ以上のパターンを同時置換したい場合、ネストが深くなって可読性が下がります。PostgreSQL・MySQL 8.0+・Oracle・SQL Server 2016+ではREGEXP_REPLACEが使えます。

UPDATE文でテーブルデータを一括修正する

REPLACEはSELECTだけでなく、UPDATE文と組み合わせてテーブルのデータを直接修正する場面でも頻用されます。

UPDATE + REPLACEの安全な手順
-- STEP1: 対象件数を確認（必ずSELECTで先に確認）
SELECT COUNT(*) FROM products
WHERE  product_code LIKE '%-OLD-%';

-- STEP2: 変更前後の内容を確認
SELECT product_code,
       REPLACE(product_code, '-OLD-', '-NEW-') AS new_code
FROM   products
WHERE  product_code LIKE '%-OLD-%';

-- STEP3: UPDATE を実行（WHERE条件は必ずつける）
UPDATE products
SET    product_code = REPLACE(product_code, '-OLD-', '-NEW-')
WHERE  product_code LIKE '%-OLD-%';

-- 件数を確認
SELECT ROW_COUNT();  -- MySQL: 更新された行数を確認
サイトURL一括変更（ドメイン移行時など）
-- サイト内リンクをHTTPからHTTPSに一括更新
UPDATE articles
SET    content = REPLACE(content, 'http://example.com', 'https://example.com')
WHERE  content LIKE '%http://example.com%';

-- 確認（UPDATE前にSELECTで）
SELECT id,
       REPLACE(content, 'http://example.com', 'https://example.com') AS new_content
FROM   articles
WHERE  content LIKE '%http://example.com%'
LIMIT  5;
UPDATE前に必ずバックアップ：REPLACE + UPDATEは対象行すべてを書き換えます。WHERE条件の漏れや検索文字列のミスで予期しないデータが壊れるリスクがあります。トランザクションを使って BEGIN; UPDATE ...; SELECT ... ; COMMIT; と確認後にCOMMITする手順を推奨します。

実務でよく使うパターン集

電話番号・郵便番号の正規化

電話番号・郵便番号の正規化（数字のみ）
-- 電話番号からハイフン・括弧・スペースを除去
SELECT
    phone AS original,
    REPLACE(REPLACE(REPLACE(REPLACE(
        phone,
    '-', ''), '(', ''), ')', ''), ' ', '') AS normalized
FROM customers;
-- '090-1234-5678' → '09012345678'
-- '(03)1234-5678' → '0312345678'

-- 郵便番号のハイフン除去
SELECT REPLACE(zip_code, '-', '') AS zip_digits
FROM addresses;
-- '123-4567' → '1234567'

-- UPDATE で正規化を適用
UPDATE customers
SET    phone = REPLACE(REPLACE(REPLACE(REPLACE(
                   phone, '-', ''), '(', ''), ')', ''), ' ', '')
WHERE  phone REGEXP '[()-\ ]';   -- MySQL

個人情報マスキング（部分置換）

マスキング: 電話番号の下4桁を****に
-- 方法1: SUBSTR で前半を残し後半を*に（MySQL / PostgreSQL）
SELECT
    CONCAT(SUBSTR(phone, 1, LENGTH(phone) - 4), '****') AS masked_phone
FROM customers;
-- '09012345678' → '0901234****'

-- 方法2: REPLACE は一致した文字列を全置換するためマスキングには不向き
-- → 先頭・末尾を固定した部分置換はSUBSTRで対応する

-- メールアドレスのドメイン部分を伏せる
SELECT
    CONCAT(SUBSTR(email, 1, INSTR(email, '@')), '****') AS masked_email
FROM users;
-- 'yamada@example.com' → 'yamada@****'

HTMLエンティティの変換（エスケープ）

HTMLエンティティの変換
-- & → &amp;、< → &lt;、> → &gt; に変換してXSS対策
SELECT
    REPLACE(
        REPLACE(
            REPLACE(user_input, '&', '&amp;'),
        '<', '&lt;'),
    '>', '&gt;')
AS escaped_html
FROM comments;

-- 逆変換（エンティティ → 文字）
SELECT
    REPLACE(
        REPLACE(
            REPLACE(html_content, '&amp;', '&'),
        '&lt;', '<'),
    '&gt;', '>')
AS decoded_html
FROM articles;

改行コードの統一・削除

改行コードの処理
-- CRLFをLFに統一（Windows→Unix形式）
SELECT REPLACE(content, CHAR(13) || CHAR(10), CHAR(10))  -- Oracle/PostgreSQL
AS unix_content FROM documents;

-- MySQLの場合
SELECT REPLACE(content, CHAR(13), '') AS content FROM documents;

-- 改行を削除してインライン表示
SELECT REPLACE(REPLACE(description, CHAR(10), ' '), CHAR(13), ' ')
AS single_line FROM products;

CSVデータの加工

CSVデータの加工・クリーニング
-- カンマ区切りのタグ文字列から特定のタグを削除
-- 'sql,mysql,oracle,postgresql' から 'oracle' を削除
SELECT REPLACE(
    REPLACE(tags, ',oracle,', ','),  -- 中間にある場合
'oracle,', '')                        -- 先頭にある場合
AS new_tags
FROM articles;

-- CSVの全角数字を半角に変換（PostgreSQL: TRANSLATE を使う方が効率的）
SELECT REPLACE(REPLACE(REPLACE(
    amount_str,
'１', '1'), '２', '2'), '３', '3')  -- …10文字ぶんネストが必要
FROM orders;
-- → 全角数字変換は TRANSLATE 関数の方が適している（後述）

REGEXP_REPLACEとの使い分け

パターンが固定文字列ならREPLACE、正規表現を使いたい場合はREGEXP_REPLACEを使います。

観点 REPLACE REGEXP_REPLACE
パターン指定 固定文字列のみ 正規表現（ワイルドカード・量詞など）
大文字/小文字無視 コレーション依存 iフラグで明示指定可能
グループ参照（後方参照） 不可 可（\1/$1等）
部分一致の削除（例: 数字を全除去） 不可（1文字ずつネスト） [0-9]で一括指定
パフォーマンス 速い やや重い（正規表現エンジン使用）
対応DBMS 全DB MySQL 8.0+ / PostgreSQL / Oracle / SQL Server 2016+
REGEXP_REPLACEが必要なケース
-- 数字をすべて除去（REPLACE では10回ネストが必要）
SELECT REGEXP_REPLACE('abc123def456', '[0-9]', '', 'g')   -- PostgreSQL: 'abcdef'
SELECT REGEXP_REPLACE('abc123def456', '[0-9]+', '')         -- MySQL 8.0+: 'abcdef'
SELECT REGEXP_REPLACE('abc123def456', '[0-9]', '')          -- Oracle: 'abcdef'

-- 電話番号の形式を統一（数字以外を除去）
SELECT REGEXP_REPLACE(phone, '[^0-9]', '', 'g')             -- PostgreSQL
FROM   customers;

-- 大文字/小文字を無視した置換
SELECT REGEXP_REPLACE('Hello HELLO hello', 'hello', 'HI', 1, 0, 'i')  -- Oracle
FROM   dual;

-- 連続する複数スペースを1つに
SELECT REGEXP_REPLACE('Hello   World', '  +', ' ')          -- MySQL 8.0+
SELECT REGEXP_REPLACE('Hello   World', '[[:space:]]+', ' ') -- Oracle

TRANSLATE関数との違い（Oracle・PostgreSQL）

TRANSLATE(文字列, 変換前, 変換後)は「1文字単位」での置換を行います。REPLACEがパターン文字列を丸ごと置換するのに対し、TRANSLATEは変換前文字列の各文字を対応する変換後文字に1対1で変換します。

REPLACE vs TRANSLATE の違い
-- REPLACE: 'ab' というパターンを丸ごと置換
SELECT REPLACE('abcabc', 'ab', 'X');   -- 'XcXc'
-- 'ab' というパターン全体が 'X' に置換

-- TRANSLATE: 'a' → 'X', 'b' → 'Y' に1文字ずつ変換
SELECT TRANSLATE('abcabc', 'ab', 'XY') FROM dual;  -- Oracle
SELECT TRANSLATE('abcabc', 'ab', 'XY');             -- PostgreSQL
-- 結果: 'XYcXYc'（a→X, b→Yに個別変換）

-- TRANSLATE が特に有効なケース: 全角数字→半角数字変換
SELECT TRANSLATE(amount_str,
    '０１２３４５６７８９',
    '0123456789')
FROM orders;  -- Oracle / PostgreSQL
-- '１２３４' → '1234' を1回で処理可能（REPLACEでは10回ネスト必要）

-- 特定文字を削除（変換後文字列を短くする）
-- Oracle: TRANSLATE での削除は第3引数のマッピングから除外することで実現
SELECT TRANSLATE('Hello123World', '0123456789', ' ') FROM dual;
-- 数字が半角スペースに変換される（削除はTRANSLATEでは直接できないためREPLACEと組み合わせ）
観点 REPLACE TRANSLATE
置換単位 パターン文字列単位 1文字単位（文字マッピング）
全角→半角変換 10回ネスト必要 1回で済む（推奨）
特定文字削除 REPLACE(col, ‘x’, ”) で簡単 マッピング省略で対応（複雑）
対応DBMS 全DB Oracle / PostgreSQL（MySQL・SQL Server は非対応）

DBMS別の動作と注意点

MySQL
-- MySQL: collationに依存して大文字/小文字の扱いが変わる
-- utf8mb4_general_ci (CI): 大文字/小文字を区別しない
-- utf8mb4_bin / utf8mb4_0900_as_cs (CS): 区別する

-- 現在のcollationを確認
SHOW VARIABLES LIKE 'collation_connection';

-- 明示的にBINARYで大文字/小文字を区別させる
SELECT REPLACE(BINARY 'Hello', 'hello', 'HI');   -- 'Hello'（置換されない）

-- MySQL 8.0+: REGEXP_REPLACE でiフラグを使う方が確実
SELECT REGEXP_REPLACE('Hello HELLO', 'hello', 'HI', 1, 0, 'i');  -- 'HI HI'
PostgreSQL
-- PostgreSQL: デフォルトで大文字/小文字を区別する（COLLATE依存）
SELECT REPLACE('Hello World', 'hello', 'HI');   -- 'Hello World'（置換されない）
SELECT REPLACE('Hello World', 'Hello', 'HI');   -- 'HI World'

-- 大文字/小文字を無視した置換（REGEXP_REPLACE + iフラグ）
SELECT REGEXP_REPLACE('Hello HELLO', 'hello', 'HI', 'ig');  -- 'HI HI'

-- TRANSLATE が使えるのはPostgreSQLとOracleのみ
SELECT TRANSLATE('abc', 'abc', 'ABC');   -- 'ABC'
Oracle
-- Oracle: 大文字/小文字を区別する（NLS_SORT設定に関わらずREPLACEは区別）
SELECT REPLACE('Hello World', 'hello', 'HI') FROM dual;  -- 'Hello World'
SELECT REPLACE('Hello World', 'Hello', 'HI') FROM dual;  -- 'HI World'

-- 大文字/小文字を無視した置換
SELECT REGEXP_REPLACE('Hello HELLO', 'hello', 'HI', 1, 0, 'i') FROM dual;

-- TRANSLATE（Oracle独自機能として特に強力）
SELECT TRANSLATE('Hello123', '0123456789', '0123456789') FROM dual;
-- 同じなので変化なし。数字を除去したい場合は REGEXP_REPLACE を使う
SQL Server
-- SQL Server: デフォルトは大文字/小文字を区別しない（CI collation）
SELECT REPLACE('Hello World', 'hello', 'HI');   -- 'HI World'（CI環境）

-- 明示的に大文字/小文字を区別させる（COLLATE CS）
SELECT REPLACE('Hello World' COLLATE Latin1_General_CS_AI, 'hello', 'HI');
-- 'Hello World'（置換されない）

-- SQL Server 2016+: STRING_ESCAPE で特殊文字をエスケープ
-- TRANSLATE は SQL Server 2017+ で利用可能
SELECT TRANSLATE('Hello123', '0123456789', '##########');
-- 'Hello##########'（10文字ぶん#に変換）

よくある質問

QREPLACEで最初の1件だけ置換するには？
AREPLACE関数はマッチしたすべての箇所を置換します。最初の1件だけ置換したい場合は、REGEXP_REPLACEの出現回数引数を使います。Oracle: REGEXP_REPLACE(str, 'pattern', 'repl', 1, 1)（第5引数が出現回数）、PostgreSQL: REGEXP_REPLACE(str, 'pattern', 'repl')はデフォルトで最初の1件のみ（gフラグなし）。
QREPLACEで大文字/小文字を無視して置換するには？
A標準のREPLACEには大文字/小文字を無視するオプションがありません。①LOWER()やUPPER()で大文字/小文字を統一してからREPLACEする方法、②REGEXP_REPLACEでiフラグを使う方法のどちらかで対応してください。
QREPLACE対象のカラムにNULLが含まれる場合の対処法は？
AREPLACE(COALESCE(col, ''), 検索, 置換) のようにCOALESCEでNULLを空文字に変換してからREPLACEを適用します。NULLをそのまま保持したい行にはCASE WHEN col IS NULL THEN NULL ELSE REPLACE(col, ...) ENDを使います。
Q複数の文字を一括置換するには（REPLACE vs TRANSLATE vs REGEXP_REPLACE）？
A・パターンが少なく固定（3個以下）: REPLACE をネスト
・1文字→1文字の大量変換（全角→半角など）: TRANSLATE（Oracle/PostgreSQL）
・正規表現で柔軟に（数字を全除去など）: REGEXP_REPLACE
ネストは5段階を超えると可読性が著しく落ちるため、TRANSLATE または REGEXP_REPLACE への切り替えを検討してください。
QUPDATE + REPLACE で元に戻せなくなった場合は？
AUPDATE前にトランザクションを開始していればROLLBACKで戻せます（BEGIN; UPDATE ...; ROLLBACK;）。トランザクション外でCOMMIT済みの場合は、バックアップからのリストアが必要です。本番環境では必ずBEGINでトランザクションを開始し、SELECTで変更内容を確認してからCOMMITする習慣をつけましょう。

まとめ

SQLのREPLACE関数は「固定文字列を別の文字列に全件置換する」シンプルな関数ですが、知っておくべき落とし穴と使い分けのポイントがあります。

  • 全件置換: マッチしたすべての箇所が置換される（1件だけはREGEXP_REPLACEで）
  • 大文字/小文字: コレーション次第（無視したい場合はLOWER() + REPLACE か REGEXP_REPLACE）
  • NULL: 3引数のいずれかがNULLなら結果はNULL（COALESCEで事前変換）
  • 削除: 第3引数を空文字 '' にすれば削除として使える
  • 複数パターン: ネストで対応。5種類超はREPLACEまたはTRANSLATEを検討
  • UPDATE前の確認: SELECT→BEGIN→UPDATE→確認→COMMITの手順を守る
使い分けのまとめ：固定文字列の置換 → REPLACE、1文字単位の大量変換 → TRANSLATE（Oracle/PostgreSQL）、正規表現・大文字小文字無視 → REGEXP_REPLACE、前後の特定文字除去 → TRIM（TRIM完全ガイド参照）

関連記事：TRIM関数完全ガイド（指定文字の除去）、SQLでスペースを削除する方法、LENGTH関数で文字列の長さを取得する方法