SQL REPLACE 関数とは？初心者向けに基本を解説：構文から応用まで徹底マスター
データ分析、アプリケーション開発、レポート作成など、SQLは様々な場面で利用されます。そして、これらの作業において、データベースに格納されている文字列データを適切に処理することは非常に重要です。例えば、入力ミスによる表記の揺れを統一したり、特定の文字や単語を別のものに置き換えたり、不要な文字を削除したりといった操作が必要になることがよくあります。
このような文字列操作を実現するための強力なツールの一つが、今回解説するSQLのREPLACE関数です。REPLACE関数を使えば、文字列の中から指定した部分文字列を検索し、別の文字列に置き換えることができます。
この記事では、SQLのREPLACE関数について、SQL初心者の方でも完全に理解できるよう、以下の点を網羅的に解説します。
REPLACE関数の基本的な構文と使い方- 様々な具体例を通じた理解の深化
- 他の文字列操作関数との違いや組み合わせ方
- 使用する上での注意点（大文字/小文字の区別、NULL値、パフォーマンスなど）
- 主要なデータベースシステムごとの違い
- 実践的な応用例
- 理解度を確認するための練習問題
この記事を読めば、SQLのREPLACE関数をマスターし、文字列操作のスキルを向上させることができるでしょう。ぜひ最後までお読みください。
1. はじめに：なぜ文字列操作が重要なのか
データベースには、数値や日付、真偽値だけでなく、氏名、住所、商品名、コメント、商品説明文など、様々な種類の文字列データが格納されています。これらの文字列データは、必ずしも常に完璧な形式で保存されているわけではありません。
例えば、
- 住所の「丁目」が「-」（ハイフン）で入力されている場合や、「番地」が「-」や「Banchi」など複数の形式で入力されている場合。
- 電話番号にハイフンが入っていたり、入っていなかったり、区切り文字がスペースやドットだったりする場合。
- 商品名に不要な記号や全角スペースが含まれている場合。
- ウェブサイトのURLに追跡用の不要なパラメータが含まれている場合。
このようなデータの「汚れ」や「表記の揺れ」は、集計や分析を正確に行う上で大きな障害となります。また、アプリケーションで表示する際に、特定のフォーマットに整形する必要がある場合もあります。
SQLの文字列操作関数は、このような問題を解決するために存在します。REPLACE関数は、その中でも特に「特定の文字列を別の文字列に置き換える」という、非常に一般的で利用頻度の高い操作を担当します。
これからREPLACE関数の使い方を学び、文字列データを思い通りに加工できるようになりましょう。
2. REPLACE関数の基本の「キ」
まずは、REPLACE関数の基本的な構文と、それぞれの引数が何を意味するのかを見ていきましょう。
2.1. 構文
SQLにおけるREPLACE関数の基本的な構文は以下の通りです。
sql
REPLACE(string, old_substring, new_substring)
この関数は、3つの引数を取り、1つの値を返します。
2.2. 引数の説明
string:- これが操作の対象となる文字列です。
- テーブルのカラム名を指定することも、
'Hello World'のような固定の文字列（リテラル）を直接記述することもできます。 - データ型は通常、
VARCHAR,TEXT,NVARCHARなどの文字列型です。もし数値型や日付型のカラムを指定した場合、多くのデータベースシステムでは自動的に文字列型に変換してくれますが、意図しない結果を防ぐため、明示的に文字列型に変換する関数（例:CAST,CONVERT) を使う方が安全な場合もあります。 - この引数が
NULLの場合、REPLACE関数の結果はNULLになります。
old_substring:- これが検索し、置き換えたい部分文字列です。
- これも文字列型のリテラルか、文字列型の値を返す式を指定します。
- この引数が
NULLの場合、REPLACE関数の結果はNULLになります。 - この引数に空文字列
''を指定できるかどうかは、データベースシステムによって挙動が異なることがありますが、一般的には指定できないか、指定しても何も置換されないことが多いです。標準的には、空文字列は検索対象とはなりません。
new_substring:- これが
old_substringが見つかった場合に、代わりに挿入される新しい部分文字列です。 - これも文字列型のリテラルか、文字列型の値を返す式を指定します。
- この引数が
NULLの場合、REPLACE関数の結果はNULLになります。 - この引数に空文字列
''を指定すると、old_substringが見つかった場所からその部分文字列が削除されたのと同じ結果になります。
- これが
2.3. 戻り値
REPLACE関数は、stringの中から見つかったold_substringの全てをnew_substringに置き換えた新しい文字列を返します。元のstring自体が変更されるわけではありません（SELECT文で使う場合）。
- もし
stringの中にold_substringが見つからなかった場合、REPLACE関数は元のstringをそのまま返します。 - もし
string,old_substring,new_substringのいずれかがNULLであれば、結果はNULLになります。
2.4. 基本的な使い方（簡単な例）
最もシンプルな例を見てみましょう。固定の文字列の中から、特定の単語を別の単語に置き換える場合です。
sql
SELECT REPLACE('Hello World', 'World', 'SQL');
このクエリを実行すると、以下のような結果が得られます。
| (No column name) |
|---|
| Hello SQL |
元の文字列 'Hello World' から 'World' という部分を探し、それを 'SQL' に置き換えています。
次に、old_substringが見つからなかった場合の例です。
sql
SELECT REPLACE('Hello World', 'Goodbye', 'SQL');
'Hello World' の中に 'Goodbye' は見つかりません。この場合、元の文字列がそのまま返されます。
| (No column name) |
|---|
| Hello World |
最後に、new_substringに空文字列を指定する例です。これは、特定の文字列を「削除」したい場合に利用できます。
sql
SELECT REPLACE('Hello World', 'World', '');
'World' を空文字列に置き換えているので、結果は 'Hello ' となります。
| (No column name) |
|---|
| Hello |
このように、REPLACE関数は非常に直感的で使いやすい関数です。
3. 具体的な使用例：実践で役立つREPLACEの使い方
ここからは、実際のデータベース操作でよく遭遇する状況を想定して、REPLACE関数の様々な使用例を見ていきましょう。テーブルのカラムに対してREPLACE関数を適用する例を中心に解説します。
架空のproductsテーブルがあるとします。このテーブルには、product_nameとdescriptionという文字列型のカラムが含まれています。
“`sql
— products テーブルの例
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
description TEXT
);
INSERT INTO products (product_id, product_name, description) VALUES
(1, ‘Apple Pie’, ‘Delicious Apple flavored pie.’),
(2, ‘Orange Juice’, ‘Fresh Orange juice, 1 liter.’),
(3, ‘Banana Split’, ‘Classic Banana dessert.’),
(4, ‘Apple iphone’, ‘Latest smartphone from Apple.’);
“`
3.1. 単一の文字列中の置換
product_nameカラムに含まれる 'Apple' を 'りんご' に置き換えて表示したいとします。
sql
SELECT
product_id,
product_name,
REPLACE(product_name, 'Apple', 'りんご') AS replaced_name
FROM
products;
結果：
| product_id | product_name | replaced_name |
|---|---|---|
| 1 | Apple Pie | りんご Pie |
| 2 | Orange Juice | Orange Juice |
| 3 | Banana Split | Banana Split |
| 4 | Apple iphone | りんご iphone |
product_nameに'Apple'が含まれる行（product_id 1 と 4）で、'Apple'が'りんご'に置き換えられているのがわかります。'Orange Juice'と'Banana Split'の行は'Apple'を含まないので、元のproduct_nameがそのまま表示されています。
3.2. 特定の文字の置換（例: スペースをハイフンに）
product_nameに含まれるスペースをハイフン - に置き換えて、URLフレンドリーな文字列を作成したいとします。
sql
SELECT
product_id,
product_name,
REPLACE(product_name, ' ', '-') AS url_friendly_name
FROM
products;
結果：
| product_id | product_name | url_friendly_name |
|---|---|---|
| 1 | Apple Pie | Apple-Pie |
| 2 | Orange Juice | Orange-Juice |
| 3 | Banana Split | Banana-Split |
| 4 | Apple iphone | Apple-iphone |
各単語間のスペースがハイフンに置き換えられています。
3.3. 複数の文字列中の置換
descriptionカラムに含まれる 'Apple' を 'りんご' に、さらに 'Orange' を 'オレンジ' に置き換えて表示したい場合はどうでしょうか？ REPLACE関数は一度に一つの置換しか行えませんが、関数をネスト（入れ子に）することで複数の置換を連続して行うことができます。
sql
SELECT
product_id,
description,
REPLACE(REPLACE(description, 'Apple', 'りんご'), 'Orange', 'オレンジ') AS replaced_description
FROM
products;
このクエリでは、まず内側のREPLACE(description, 'Apple', 'りんご')が実行され、description中の'Apple'が'りんご'に置き換えられた文字列が生成されます。次に、その生成された文字列に対して外側のREPLACE(..., 'Orange', 'オレンジ')が実行され、'Orange'が'オレンジ'に置き換えられます。
結果：
| product_id | description | replaced_description |
|---|---|---|
| 1 | Delicious Apple flavored pie. | Delicious りんご flavored pie. |
| 2 | Fresh Orange juice, 1 liter. | Fresh オレンジ juice, 1 liter. |
| 3 | Classic Banana dessert. | Classic Banana dessert. |
| 4 | Latest smartphone from Apple. | Latest smartphone from りんご. |
このように、REPLACE関数をネストすることで、複数の置換を効率的に行うことができます。ただし、ネストの回数が増えるとSQLが読みにくくなるため、置換する種類が多い場合は他の方法（例：正規表現置換関数、複数のUPDATE文など）を検討することもあります。
3.4. 大文字/小文字を区別しない置換
多くのデータベースシステムでは、REPLACE関数はデフォルトで大文字/小文字を区別して置換を行います。例えば、先ほどの例で'apple'（小文字）を検索しても、'Apple'（大文字始まり）は置換されません。
sql
SELECT
product_id,
product_name,
REPLACE(product_name, 'apple', 'りんご') AS replaced_name -- 'apple' (小文字) で検索
FROM
products;
結果：
| product_id | product_name | replaced_name |
|---|---|---|
| 1 | Apple Pie | Apple Pie |
| 2 | Orange Juice | Orange Juice |
| 3 | Banana Split | Banana Split |
| 4 | Apple iphone | Apple iphone |
product_nameに 'apple iphone' のように小文字で 'apple' が含まれていれば置換されますが、今回のデータにはありません。
もし大文字/小文字を区別せずに置換を行いたい場合は、対象文字列と検索文字列の両方を、一時的にすべて大文字にするか、すべて小文字にする関数と組み合わせて使用するのが一般的です。SQLにはUPPER関数（全て大文字に変換）とLOWER関数（全て小文字に変換）があります。
例えば、対象文字列と検索文字列の両方を小文字に変換してからREPLACEを行い、その結果を元の文字列のケースに合わせる…という操作はREPLACE単体ではできません。しかし、「大文字/小文字を区別せず検索し、指定した新しい文字列に置き換える」という目的であれば、以下のようにLOWER関数と組み合わせることができます。
sql
SELECT
product_id,
product_name,
-- 元の文字列を小文字化し、検索文字列も小文字化してREPLACEを実行
REPLACE(LOWER(product_name), 'apple', 'りんご') AS lower_replaced_name
FROM
products;
結果：
| product_id | product_name | lower_replaced_name |
|---|---|---|
| 1 | Apple Pie | りんご pie |
| 2 | Orange Juice | orange juice |
| 3 | Banana Split | banana split |
| 4 | Apple iphone | りんご iphone |
この方法では、結果文字列も全て小文字になってしまいます。もし元の文字列のケースをある程度維持したい（例えば、Appleはりんごに、appleもりんごに、というように検索はケース非依存で行いたいが、結果は全てりんごという文字列にしたい）場合は、この方法で十分です。
データベースシステムによっては、大文字/小文字の区別に関する設定（COLLATION）を変更することで、REPLACEを含む文字列比較や操作のデフォルト挙動を変えることも可能ですが、これはデータベース全体の挙動に影響を与えるため、注意が必要です。特定のクエリ内でのみケース非依存の置換を行いたい場合は、LOWERまたはUPPER関数と組み合わせるのが最もポータブルで安全な方法です。この点については、後の「データベースシステムごとの違い」や「注意点」のセクションで詳しく解説します。
3.5. NULL値の扱い
REPLACE関数のいずれかの引数にNULLが含まれている場合、結果はNULLになります。
“`sql
SELECT REPLACE(‘Hello World’, ‘World’, NULL); — new_substring が NULL
— 結果: NULL
SELECT REPLACE(‘Hello World’, NULL, ‘SQL’); — old_substring が NULL
— 結果: NULL
SELECT REPLACE(NULL, ‘World’, ‘SQL’); — string が NULL
— 結果: NULL
“`
これは、SQL関数の一般的な挙動です。もし、stringカラムにNULLが含まれる可能性がある場合に、REPLACEを適用してもNULLにしたくない（例：NULLの場合は空文字列として扱いたい）場合は、COALESCEなどの関数と組み合わせる必要があります。
例：descriptionカラムがNULLの場合に空文字列を返すようにしてからREPLACEを適用する
sql
SELECT
product_id,
description,
REPLACE(COALESCE(description, ''), 'Apple', 'りんご') AS replaced_description_handling_null
FROM
products;
COALESCE(description, '')は、descriptionがNULLの場合は空文字列''を返し、そうでない場合はdescriptionの値をそのまま返します。これにより、REPLACE関数の最初の引数がNULLになることを防ぎ、他の引数が非NULLであれば必ず文字列が返されるようになります。
3.6. 数値や日付を文字列として扱う場合の置換
REPLACE関数は文字列関数なので、数値型や日付型のカラムに直接適用しようとするとエラーになるか、暗黙の型変換によって予期しない結果になる可能性があります。このような場合は、明示的に型変換関数（例: CAST, CONVERT）を使って一度文字列に変換してからREPLACEを適用します。
例：order_idという数値カラム（INT型）があるとします。これを文字列として扱い、先頭に'ORDER-'という文字列を追加したいが、その前に数値自体に含まれる特定の桁（例えば 0）を X に置き換えたいとします。
“`sql
— order_id が INT 型の orders テーブルを想定
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE
);
INSERT INTO orders (order_id, order_date) VALUES
(1001, ‘2023-01-15’),
(2030, ‘2023-01-16’),
(3300, ‘2023-01-17’);
“`
order_idの0をXに置き換え、さらに先頭に'ORDER-'を付けたい場合：
sql
SELECT
order_id,
CAST(order_id AS VARCHAR(10)) AS order_id_string, -- 数値をVARCHARに変換
REPLACE(CAST(order_id AS VARCHAR(10)), '0', 'X') AS replaced_id_string, -- '0'を'X'に置換
'ORDER-' || REPLACE(CAST(order_id AS VARCHAR(10)), '0', 'X') AS formatted_order_id -- 先頭に文字列追加 (||はPostgreSQL/Oracleなど。SQL Serverは+、MySQLはCONCAT)
FROM
orders;
結果例（PostgreSQL/Oracleの場合）：
| order_id | order_id_string | replaced_id_string | formatted_order_id |
|---|---|---|---|
| 1001 | 1001 | 1XX1 | ORDER-1XX1 |
| 2030 | 2030 | 2X3X | ORDER-2X3X |
| 3300 | 3300 | 33XX | ORDER-33XX |
このように、数値や日付を文字列として操作したい場合は、明示的な型変換が必要になります。
4. REPLACE関数と他の文字列操作関数
SQLにはREPLACE以外にも様々な文字列操作関数があります。REPLACE関数はこれらの関数と組み合わせて使うことで、より複雑な文字列処理を実現できます。ここでは、代表的な関数とREPLACEとの関連性を見ていきましょう。
SUBSTRING/SUBSTR: 指定した位置から指定した長さだけ文字列を切り出す関数です。REPLACEは文字列内のパターンを探して置換するのに対し、SUBSTRINGは位置で指定します。特定の場所にある文字だけを置換したい場合、SUBSTRINGで取り出し、REPLACEで処理し、CONCATで元の文字列に戻す、といった組み合わせが考えられます。LEFT/RIGHT: 文字列の左端から、または右端から、指定した長さだけを切り出す関数です。LENGTH/CHAR_LENGTH/LEN: 文字列の長さを返します。REPLACEによって文字列長がどのように変化するかを確認したり、特定の長さの文字列だけを処理対象とするWHERE句の条件に使ったりできます。CONCAT/||/+: 複数の文字列を結合する関数/演算子です。REPLACEの置換結果と他の文字列を結合する際によく使われます（前述の数値カラムの例を参照）。TRIM/LTRIM/RTRIM: 文字列の先頭や末尾、または両端から特定の文字（デフォルトは空白）を取り除く関数です。REPLACEは文字列中の全ての出現箇所を置換するのに対し、TRIMは先頭/末尾の不要な文字を除去するのに特化しています。全角スペースやタブなど、空白以外の文字を先頭/末尾から取り除きたい場合は、TRIMではなくREPLACEを使う、あるいはTRIMの引数で指定できる場合もあります。-
LIKE: 特定のパターン（ワイルドカード_,%を使用）にマッチするかどうかを判定する演算子です。REPLACEは置換を行う関数ですが、REPLACEを適用する行を絞り込むために、WHERE句でLIKE演算子を使うことがよくあります。例えば、「説明文に’Apple’が含まれる商品の、説明文中の’Apple’を置換する」といった場合です。sql
SELECT
product_id,
description,
REPLACE(description, 'Apple', 'りんご') AS replaced_description
FROM
products
WHERE
description LIKE '%Apple%'; -- 説明文に 'Apple' が含まれる行だけを対象にする
* 正規表現関数 (REGEXP_REPLACEなど): MySQL, PostgreSQL, Oracleなどの一部のデータベースシステムは、正規表現を使った文字列操作関数を提供しています。REGEXP_REPLACEは、固定文字列ではなく、より複雑なパターン（例：数字の並び、特定の文字の繰り返しなど）を検索して置換できます。REPLACEは固定文字列の置換に特化しており、シンプルで高速ですが、正規表現ほど柔軟ではありません。複雑なパターン置換が必要な場合は正規表現関数が強力ですが、単純な置換であればREPLACEの方が分かりやすくパフォーマンスも良いことが多いです。
これらの関数を適切に組み合わせることで、様々な文字列処理の要求に対応できるようになります。REPLACE関数は、これらのツールボックスの中で最も基本的なツールの一つと言えるでしょう。
5. REPLACE関数を使う上での注意点
REPLACE関数は便利ですが、使用する際にはいくつか注意すべき点があります。これらの点を理解しておかないと、意図しない結果になったり、パフォーマンスの問題に直面したりする可能性があります。
5.1. 大文字/小文字の区別（Case Sensitivity）
前述の通り、多くのデータベースシステムでは、REPLACE関数はデフォルトで大文字/小文字を区別します（Case Sensitive）。しかし、この挙動はデータベースシステム、オペレーティングシステム、およびデータベース/テーブル/カラムのCOLLATION設定によって異なります。
- PostgreSQL, Oracle: デフォルトで大文字/小文字を区別します。
- SQL Server: デフォルトでは大文字/小文字を区別しない設定（Case Insensitive）になっていることが多いですが、インストール時やデータベース/カラム作成時のCOLLATION設定によって異なります。
- MySQL: オペレーティングシステムの設定やインストール時の設定、テーブル/カラムのCOLLATION設定によって挙動が異なります。Linux上ではデフォルトで区別することが多いですが、Windows上では区別しないことが多いです。
常に意図した挙動を実現するためには:
- Case Sensitiveな置換をしたい場合: 大文字/小文字の区別をデフォルトでしない設定になっているシステム（例: SQL Server, Windows上のMySQL）では、明示的にケースセンシティブな比較を行うためのCOLLATIONを指定するか、バイナリ比較 (
BINARY演算子など) を利用する必要がある場合があります。 - Case Insensitiveな置換をしたい場合:
LOWER()またはUPPER()関数を、対象文字列と検索文字列の両方に適用するのが最も一般的でポータブルな方法です。（前述の例を参照:REPLACE(LOWER(string), LOWER(old_substring), new_substring)) この場合、結果は全て小文字（または大文字）になります。
使用しているデータベースシステムのデフォルト設定や、必要に応じてCOLLATIONについて理解しておくことが重要です。
5.2. パフォーマンスへの影響
REPLACE関数は、特にUPDATE文で大量のデータが含まれるカラムに対して適用する場合、パフォーマンスに影響を与える可能性があります。
SELECT文での利用:SELECT REPLACE(column, ..., ...)のように使う場合、元のデータは変更されず、関数の結果が表示されるだけなので、一般的にパフォーマンスへの影響は限定的です。ただし、非常に大きな文字列に対して関数を適用し、その結果を大量に取得する場合は、それなりにCPUリソースを消費します。UPDATE文での利用:UPDATE table SET column = REPLACE(column, ..., ...)のように使う場合、実際にテーブルのデータが書き換わります。これは以下の点でパフォーマンスに影響する可能性があります。- インデックスの無効化: 対象のカラムにインデックスが貼られていても、
REPLACE関数を通した値で更新すると、多くの場合、そのインデックスは更新処理に直接利用されません。また、WHERE REPLACE(column, ..., ...) = '...'のように、WHERE句で関数を使って検索条件を指定すると、そのカラムに貼られているインデックスが利用されにくくなり（関数インデックスなど特殊な場合を除く）、フルスキャンが発生しやすくなり、検索性能が著しく低下する可能性があります。 - データの書き換えコスト: 大量の行を更新する場合、ディスクへの書き込み処理が多く発生し、I/O負荷が増大します。
- ロック:
UPDATE文は対象行やテーブルにロックをかけるため、他の処理に影響を与える可能性があります。
- インデックスの無効化: 対象のカラムにインデックスが貼られていても、
パフォーマンスに関する対策:
UPDATE文を実行する前に、処理対象となる行数を確認する。- 処理対象が多い場合は、ピークタイムを避けて実行する。
- 大きなテーブルに対してUPDATEを行う際は、一度に全ての行を処理するのではなく、少量ずつバッチ処理することを検討する。
WHERE句でREPLACE関数の結果を使って検索条件を指定することは、可能な限り避ける。もし必要なら、代替手段（例: 全文検索機能、別のカラムに正規化されたデータを格納するなど）を検討する。- どうしても関数を使った検索が必要で、かつ頻繁にその条件で検索する場合は、関数ベースインデックス（一部のデータベースシステムがサポート）の利用を検討する。
5.3. 永続的な変更 vs. 一時的な表示
SELECT文でREPLACE関数を使っても、元のデータベースのデータは一切変更されません。単に、クエリの結果として表示されるデータが加工されるだけです。
“`sql
— REPLACE関数を使って表示を加工する
SELECT product_name, REPLACE(product_name, ‘ ‘, ‘-‘) AS formatted_name FROM products;
— 元のデータを SELECT すると、変更されていないことがわかる
SELECT product_name FROM products WHERE product_id = 1; — 結果は ‘Apple Pie’ のまま
“`
一方、UPDATE文でREPLACE関数を使うと、データベースに格納されているデータそのものが永続的に変更されます。
“`sql
— product_name のスペースをハイフンに置き換えてデータベースに保存する
UPDATE products
SET product_name = REPLACE(product_name, ‘ ‘, ‘-‘)
WHERE product_id = 1; — 例として1行だけ更新
— 更新後のデータを SELECT すると、変更されていることがわかる
SELECT product_name FROM products WHERE product_id = 1; — 結果は ‘Apple-Pie’ に変わっている
“`
UPDATE文を使用する際は、変更を取り消すことが難しい場合が多いため、必ず事前にデータのバックアップを取るか、変更対象の行数を十分に確認してから実行してください。 特に、WHERE句を指定しないUPDATE文は、テーブル全体のデータを書き換えてしまうため、非常に危険です。
5.4. ネストの可読性
複数の置換を行うためにREPLACE関数をネストすることは可能ですが、ネストのレベルが深くなるとSQLクエリの可読性が著しく低下します。
例えば、'a'を'X'に、'b'を'Y'に、'c'を'Z'に置換する場合：
sql
REPLACE(REPLACE(REPLACE(string, 'a', 'X'), 'b', 'Y'), 'c', 'Z')
このように、どの置換が最初に行われ、どの置換が最後に行われるのかを追うのが難しくなります。少数の置換であれば問題ありませんが、多数の置換が必要な場合は、アプリケーション側で処理を行うか、データベースによっては正規表現置換関数（より柔軟な置換ルールを記述できる）を検討する方が良い場合もあります。
5.5. 空文字列 ('') の扱い
REPLACE(string, old_substring, new_substring)関数において：
old_substringに空文字列''を指定した場合、ほとんどのデータベースシステムでエラーになるか、期待通りの挙動をしません。標準SQLでは、空文字列は検索対象とはならないため、old_substringとして指定すべきではありません。特定の文字を削除したい場合は、new_substringに空文字列を指定します（REPLACE(string, '削除したい文字', '')）。new_substringに空文字列''を指定した場合、old_substringが見つかった箇所が削除されます。これは特定の文字列を「削除する」ための一般的な方法です。
6. データベースシステムごとのREPLACE関数
REPLACE関数は多くの主要なデータベースシステムでサポートされており、基本的な構文や挙動は共通しています。しかし、細かい点や関連する関数には違いがあります。
- MySQL:
REPLACE(string, old_substring, new_substring)– 標準的な挙動です。大文字/小文字の区別はCOLLATION設定に依存します。CONCAT関数または||演算子で文字列結合を行います。 - PostgreSQL:
REPLACE(string, old_substring, new_substring)– 標準的な挙動で、デフォルトでは大文字/小文字を区別します。||演算子で文字列結合を行います。より高度なパターン置換にはREGEXP_REPLACE関数があります。 - SQL Server:
REPLACE(string, old_substring, new_substring)– 標準的な挙動で、デフォルトでは大文字/小文字を区別しないことが多いです（COLLATION設定による）。+演算子で文字列結合を行います。 - Oracle:
REPLACE(string, old_substring, new_substring)– 標準的な挙動で、デフォルトでは大文字/小文字を区別します。||演算子で文字列結合を行います。- Oracleには
TRANSLATEという似た関数がありますが、これはREPLACEとは異なります。TRANSLATE(string, from_string, to_string)は、stringに含まれるfrom_stringの各文字を、対応するto_stringの文字に1対1で置き換えます。from_stringに存在し、to_stringに対応する文字がない場合、その文字は削除されます。 - 例:
REPLACE('aabbcc', 'ab', 'XY')->'XYXYcc'(abという文字列をXYに置換) - 例:
TRANSLATE('aabbcc', 'ab', 'XY')->'XXYYcc'(aをXに、bをYに文字単位で置換) - 例:
TRANSLATE('abcde', 'acd', 'XZ')->'XbZe'(aをXに,cをZに,dはto_stringにないので削除) - このように、
REPLACEは部分文字列単位、TRANSLATEは文字単位の置換を行います。用途によって使い分ける必要があります。
- Oracleには
- SQLite:
REPLACE(string, old_substring, new_substring)– 標準的な挙動です。
ほとんどの環境でREPLACE関数は利用できますが、大文字/小文字の区別の挙動や、文字列結合の演算子（+ vs || vs CONCAT）などはシステムによって異なるため、使用しているデータベースシステムのドキュメントを確認することをお勧めします。
7. 実践的な応用例
これまでに学んだことを活かして、もう少し複雑で実践的な応用例を見てみましょう。
7.1. データのクレンジング（不要な記号や文字の除去）
顧客の電話番号カラムphone_numberに、ハイフン-、スペース、括弧()などの記号が混在している場合を想定します。これらの不要な記号をすべて削除し、数字だけの形式に統一したいとします。
“`sql
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
phone_number VARCHAR(50)
);
INSERT INTO customers (customer_id, customer_name, phone_number) VALUES
(1, ‘山田 太郎’, ‘090-1234-5678’),
(2, ‘佐藤 花子’, ‘(080) 9876-5432’),
(3, ‘田中 一郎’, ‘070 1111 2222’);
“`
電話番号から-, , (, ) をすべて削除するには、REPLACE関数をネストして使用します。
sql
SELECT
customer_id,
customer_name,
phone_number,
REPLACE(REPLACE(REPLACE(REPLACE(phone_number, '-', ''), ' ', ''), '(', ''), ')', '') AS cleaned_phone_number
FROM
customers;
結果：
| customer_id | customer_name | phone_number | cleaned_phone_number |
|---|---|---|---|
| 1 | 山田 太郎 | 090-1234-5678 | 09012345678 |
| 2 | 佐藤 花子 | (080) 9876-5432 | 08098765432 |
| 3 | 田中 一郎 | 070 1111 2222 | 07011112222 |
このように、複数の不要な文字を削除する場合にもREPLACE関数が役立ちます。ネストの順番は結果に影響しません（どの文字を先に削除しても最終的に同じになります）。
7.2. HTMLタグの除去（簡単なケース）
もしテキストカラムに簡単なHTMLタグが含まれており、これを除去してプレーンテキストとして表示したい場合、REPLACE関数で対応できることもあります。ただし、これは非常に限定的なケース（特定のタグだけ、属性なしなど）に限られます。複雑なHTMLのパースや除去には正規表現や専用のライブラリが必要です。
例：<br>タグだけを除去したい場合
sql
SELECT REPLACE('これは<br>改行です。<br>次の行です。', '<br>', '');
-- 結果: これは改行です。次の行です。
例：タグを除去したい場合（開始タグと終了タグ）
sql
SELECT REPLACE(REPLACE('<b>重要な</b>情報', '<b>', ''), '</b>', '');
-- 結果: 重要な情報
このように、特定の固定タグであればREPLACEで除去できます。しかし、<a href="...">のような属性付きタグや、ネストしたタグ、様々な種類のタグが混在する場合はREPLACEだけでは対応できません。
7.3. データ匿名化（簡単なケース）
個人情報などが含まれるデータを共有・分析する際に、一部を匿名化したい場合があります。例えば、メールアドレスのユーザー名の部分を***に置き換えるといった場合です。これはREPLACEだけでは難しいことが多いですが（@より前を特定する必要があるため、SUBSTRINGや正規表現が必要）、特定の固定文字列を匿名化するシンプルなケースであれば利用できます。
例：製品説明文中の固有名詞'Apple'を'*****'に置き換える
sql
SELECT
product_id,
description,
REPLACE(description, 'Apple', '*****') AS anonymized_description
FROM
products;
結果：
| product_id | description | anonymized_description |
|---|---|---|
| 1 | Delicious Apple flavored pie. | Delicious * flavored pie. |
| 2 | Fresh Orange juice, 1 liter. | Fresh Orange juice, 1 liter. |
| 3 | Classic Banana dessert. | Classic Banana dessert. |
| 4 | Latest smartphone from Apple. | Latest smartphone from *. |
これもシンプルですが、もし'Apple'が大文字・小文字混在したり、単語の一部として現れたりする場合（例: 'Pineapple'）、REPLACEだけでは限界があります。より高度な匿名化には、やはり他の関数や正規表現が必要になります。
8. 練習問題
これまでに学んだ知識を定着させるために、いくつかの練習問題に挑戦してみましょう。
架空のemployeesテーブルがあるとします。
“`sql
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
full_name VARCHAR(100),
email VARCHAR(100),
hire_date DATE,
notes TEXT — 社員に関するメモ、表記ゆれがあるとする
);
INSERT INTO employees (employee_id, full_name, email, hire_date, notes) VALUES
(101, ‘田中 太郎’, ‘[email protected]’, ‘2020-04-01’, ‘リーダーシップあり。 プロジェクトA担当。’),
(102, ‘山田 花子’, ‘[email protected]’, ‘2021-07-15’, ‘コミュニケーション能力高い。 プロジェクトB担当’),
(103, ‘佐藤 一郎’, ‘[email protected]’, ‘2022-10-01’, ‘技術スキル優秀。プロジエクトC担当。’), — プロジエクトは入力ミス
(104, ‘高橋 由美’, ‘[email protected]’, ‘2023-01-10’, ‘新しいメンバー。プロジェクトA担当。’);
“`
以下の要件を満たすSQLクエリを作成してください。
問題1: full_nameカラムに含まれるスペースをアンダースコア_に置き換えて表示してください。
問題2: notesカラムに含まれる全角スペースを半角スペースに置き換えて表示してください。
問題3: notesカラムに含まれる入力ミスである'プロジエクト'を正しいスペルの'プロジェクト'に置き換えて表示してください。
問題4: emailカラムのドメイン部分'@example.com'を'@mycompany.com'に置き換えて表示してください。
問題5: notesカラムに含まれる'担当'という単語を削除して表示してください。
問題6: UPDATE文を使って、notesカラムに含まれる'担当'という単語を実際にデータベースから削除してください。ただし、削除前に影響を受ける行数を確認してください。
練習問題 解答例
問題1: full_nameカラムに含まれるスペースをアンダースコア_に置き換えて表示してください。
sql
SELECT
employee_id,
full_name,
REPLACE(full_name, ' ', '_') AS formatted_name
FROM
employees;
問題2: notesカラムに含まれる全角スペースを半角スペースに置き換えて表示してください。
sql
SELECT
employee_id,
notes,
REPLACE(notes, ' ', ' ') AS notes_half_space
FROM
employees;
(注: 全角スペースは環境によって入力が難しい場合があるため、コピー＆ペーストで利用してください。)
問題3: notesカラムに含まれる入力ミスである'プロジエクト'を正しいスペルの'プロジェクト'に置き換えて表示してください。
sql
SELECT
employee_id,
notes,
REPLACE(notes, 'プロジエクト', 'プロジェクト') AS notes_corrected_typo
FROM
employees;
問題4: emailカラムのドメイン部分'@example.com'を'@mycompany.com'に置き換えて表示してください。
sql
SELECT
employee_id,
email,
REPLACE(email, '@example.com', '@mycompany.com') AS new_email
FROM
employees;
問題5: notesカラムに含まれる'担当'という単語を削除して表示してください。
sql
SELECT
employee_id,
notes,
REPLACE(notes, '担当', '') AS notes_without_duty
FROM
employees;
問題6: UPDATE文を使って、notesカラムに含まれる'担当'という単語を実際にデータベースから削除してください。ただし、削除前に影響を受ける行数を確認してください。
まず、影響を受ける行数を確認します。notesカラムに'担当'が含まれる行をカウントします。
sql
SELECT COUNT(*)
FROM employees
WHERE notes LIKE '%担当%';
このクエリで、'担当'が含まれる行数が表示されます。例えば、4行すべてに含まれている場合は4と表示されるでしょう。
行数を確認して問題なければ、UPDATE文を実行します。
“`sql
— まず、トランザクションを開始しておくと安全です（データベースシステムによる）
— START TRANSACTION; — MySQL, PostgreSQL
— BEGIN TRANSACTION; — SQL Server
UPDATE employees
SET notes = REPLACE(notes, ‘担当’, ”);
— 確認用のSELECT文（オプション）
— SELECT employee_id, notes FROM employees;
— 問題なければコミット
— COMMIT;
— 問題があればロールバック
— ROLLBACK;
``START TRANSACTION
**注意:** UPDATE文を実行する際は、上記のようにやBEGIN TRANSACTIONでトランザクションを開始し、変更内容を確認してからCOMMITする、あるいは事前にテーブル全体のバックアップを取得するなどの対策を強く推奨します。WHERE句がないため、テーブル全体のnotes`カラムが変更されます。
これらの練習問題を通じて、REPLACE関数の使い方がより深く理解できたかと思います。
9. まとめ
この記事では、SQLのREPLACE関数について、初心者向けに基本から応用まで詳細に解説しました。
REPLACE関数は、REPLACE(string, old_substring, new_substring)という構文で、文字列の中から指定した部分文字列old_substringを検索し、全てをnew_substringに置き換えた新しい文字列を返します。- 文字列中の特定の単語や文字の置換、不要な部分の削除（
new_substringに空文字列を指定）などに広く利用できます。 - 複数の置換を行いたい場合は、
REPLACE関数をネストして使用できます。 - デフォルトでは大文字/小文字を区別しますが、
LOWERやUPPER関数と組み合わせることで、大文字/小文字を区別しない置換も実現できます。 - いずれかの引数が
NULLの場合、結果はNULLになります。COALESCEなどを使ってNULLを回避することも可能です。 - 数値型や日付型のカラムに対して使用する場合は、明示的に文字列型に変換してから使用する必要があります。
SUBSTRING,CONCAT,TRIM,LIKEなどの他の文字列操作関数と組み合わせて使うことで、より複雑な処理が可能になります。UPDATE文で大量のデータに対してREPLACEを適用する場合や、WHERE句でREPLACE関数の結果を条件にする場合は、パフォーマンスに注意が必要です。- データベースシステムによって、大文字/小文字のデフォルト挙動や、類似の機能を持つ関数（Oracleの
TRANSLATEなど）に違いがあることを理解しておく必要があります。
REPLACE関数は、データベースに格納された文字列データを整形し、分析や表示に適した形に加工するための非常に強力で基本的なツールです。データのクレンジングやフォーマット変換など、様々な場面で活躍します。
この記事で解説した内容を参考に、ぜひご自身のSQL学習や実務でREPLACE関数を活用してみてください。他の文字列操作関数についても学び、SQLによるデータ操作の幅を広げていくことをお勧めします。
これで、SQLのREPLACE関数について、初心者の方でも十分に理解し、活用できるようになるはずです。