SQL REPLACE関数：データベース内の文字列の検索と置換

概要：このチュートリアルでは、SQL REPLACE 関数を使用して、指定された文字列内の部分文字列のすべての出現箇所を別の部分文字列に検索および置換する方法を学習します。

SQL REPLACE関数の紹介

場合によっては、列内の部分文字列を新しい部分文字列に検索して置換したい場合があります。たとえば、無効なリンクを新しいリンクに変更したり、廃止された製品の名前を新しい名前に変更したりする場合などです。

SQLは、文字列内の部分文字列のすべての出現箇所を新しい部分文字列に置き換えることができる、REPLACE と呼ばれる非常に便利な文字列関数を提供します。

以下は、REPLACE 関数の構文を示しています

REPLACE(string, old_substring, new_substring);Code language: SQL (Structured Query Language) (sql)

REPLACE 関数は、old_substring のすべての出現箇所を検索し、それを new_string に置き換えます。

次のステートメントは、bar のすべての出現箇所を foo に置き換えるため、結果は bar bar bar になります。

SELECT REPLACE('foo foo bar', 'foo', 'bar'); -- bar bar barCode language: SQL (Structured Query Language) (sql)

REPLACE 関数は、大文字と小文字を区別して部分文字列を検索することに注意してください。たとえば、次のステートメントでは foo を bar に置き換えますが、FOO は検索文字列 foo と一致しないため置き換えられません。

SELECT REPLACE('foo FOO bar', 'foo', 'bar'); -- bar FOO bar
Code language: SQL (Structured Query Language) (sql)

関数が部分文字列を見つけられない場合、何も行いません。たとえば、次のステートメントは、部分文字列 BAR の出現箇所が見つからないため、元の文字列を返します。

SELECT REPLACE('foo foo bar', 'BAR', 'bar'); -- foo foo barCode language: SQL (Structured Query Language) (sql)

UPDATEステートメントを使用したSQL REPLACE

サンプルデータベースの employees テーブルを見てみましょう。

次のステートメントは、従業員の名前と電話番号を返します。

SELECT 
    first_name, 
    last_name, 
    phone_number
FROM
    employees
ORDER BY first_name, last_name;Code language: SQL (Structured Query Language) (sql)

電話番号の書式設定に、ドット（.）文字の代わりにダッシュ（-）文字を使用したいとします。この場合、UPDATE ステートメントを使用して、phone_number 列のダッシュ文字をドット文字に置き換えます。次のステートメントのようにします。

UPDATE employees 
SET 
    phone_number = REPLACE(phone_number, '.', '-');Code language: SQL (Structured Query Language) (sql)

SQL REPLACE function replace dot with dash

上記の UPDATE ステートメントは、employees テーブルのすべての行を更新することに注意してください。

本番システムでデータを更新する場合は、最初に SELECT ステートメントを使用して、一括更新を実行する前に影響を受ける行数を確認する必要があります。

たとえば、次のステートメントは、従業員ID 100の従業員のメールを sqltutorial.org から acme.com に更新します

UPDATE employees 
SET 
    email = REPLACE(email,
        'sqltutorial.org',
        'acme.com')
WHERE
    employee_id = 100;Code language: SQL (Structured Query Language) (sql)

結果を確認しましょう。

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    email
FROM
    employees
WHERE
    employee_id = 100;    
Code language: SQL (Structured Query Language) (sql)

期待どおりに動作します。そのため、WHERE 句を削除することで、すべての行に変更を適用できます。

UPDATE employees 
SET 
    email = REPLACE(email,
        'sqltutorial.org',
        'acme.com');Code language: SQL (Structured Query Language) (sql)

REPLACE 関数の最初の引数に、リテラル文字列として列名を使用してしまうミスを犯しやすいので注意してください。以下のように。

UPDATE employees 
SET 
    email = REPLACE('email',
        'sqltutorial.org',
        'acme.com')Code language: SQL (Structured Query Language) (sql)

あなたの意図は、email 列の sqltutorial.org を acme.com に置き換えることです。しかし、このステートメントは、次の式の結果がリテラル文字列のemailであるため、email 列のすべての値を email に更新します。

REPLACE('email','sqltutorial.org','acme.com'); -- emailCode language: SQL (Structured Query Language) (sql)

このチュートリアルでは、SQL REPLACE 関数を使用して、部分文字列のすべての出現箇所を新しい文字列に検索および置換する方法を学習しました。

このチュートリアルは役に立ちましたか？