SQLで文字列を置き換える
SQLで文字列を置き換えるには、次の2つの関数が使えます。
- REPLACE関数
- TRANSLATE関数
この記事では、SQLで文字列を置き換える方法について解説します。
それぞれの使い方を見ていきましょう。
（１）REPLACE関数
REPLACE関数は、文字列内で指定した一部の文字列を、別の文字列に置き換える関数です。
次のように記述します。
SELECT REPLACE('対象の文字列', '置換前の文字列', '置換後の文字列');
実際に、SQL文を動かして確認しましょう。
SELECT REPLACE('potepan_style', 'potepan', 'sql');
上記のSQL文を実行すると、「potepan」と書かれている部分が「sql」に置き換わります。
mysql> SELECT REPLACE('potepan_style', 'potepan', 'sql');
+--------------------------------------------+
| REPLACE('potepan_style', 'potepan', 'sql') |
+--------------------------------------------+
| sql_style |
+--------------------------------------------+
1 row in set (0.00 sec)
このように、文字列が置き換えられました。
なお、置換する文字列は、大文字・小文字を区別するので完全に一致する文字列を指定する必要があります。
mysql> SELECT REPLACE('potepan_style', 'POTEPAN', 'sql');
+--------------------------------------------+
| REPLACE('potepan_style', 'POTEPAN', 'sql') |
+--------------------------------------------+
| potepan_style |
+--------------------------------------------+
1 row in set (0.00 sec)
上記の点だけ注意して使いましょう。
（２）TRANSLATE関数
TRANSLATE関数は、文字列を別の文字列に置換する関数です。
次のように記述できます。
SELECT TRANSLATE('対象の文字列', '置換前の文字列', '置換後の文字列');
ただし、TRANSLATE関数はMySQLでは使用できません。
REPLACE関数との違いは、文字列が完全一致しているかどうかです。
REPLACE関数は、第二引数に指定した文字列を単語単位で検索します。
そのため、対象の文字列の中に、置換前の文字列が一致しない場合は、文字列が置き換わりません。
TRANSLATE関数は、第二引数の文字列の先頭から1文字ずつ検索し、位置が対応している第三引数へ置換が行われます。
先ほどの例で、TRANSLATE関数を実行してみましょう。
mydb=# SELECT TRANSLATE('potepan_style', 'potepan', 'sql');
translate
-----------
sqls_slyl
(1 row)
このように、REPLACE関数とは挙動が異なりますよね。
TRANSLATE関数では、「p」が「s」に、「o」が「q」に、「t」が「l」に、置き換わるよう指定しています。
そのため、上記のような結果になりました。
単語単位で文字列を置き換えたい場合は、REPLACE関数が良いと言えます。
一方、TRANSLATE関数は、1文字単位で置き換えたい場合に有効です。
SELECT TRANSLATE('potepan_style', 'oa', 'ao');
mydb=# SELECT TRANSLATE('potepan_style', 'oa', 'ao');
translate
---------------
patepon_style
(1 row)
置き換えを複数するには？
文字列の置き換えを、一度に複数行いたい場合もあるでしょう。
その場合、REPLACE関数を入れ子で使うことで、複数の文字列を1回で置き換えできます。
記述方法は次の通りです。
SELECT REPLACE(REPLACE('対象の文字列', '置換前の文字列', '置換後の文字列'), '置換前の文字列', '置換後の文字列');
置き換えたい文字列分、REPLACE関数を入れ子にします。
少し可読性に欠けますが、期待した通りの結果が取得できます。
mysql> SELECT REPLACE(REPLACE('potepan_style', 'potepan', 'POTEPAN'), 'style', 'STYLE') AS name;
+---------------+
| name |
+---------------+
| POTEPAN_STYLE |
+---------------+
1 row in set (0.00 sec)
置き換えを複数の文字列で行いたい場合は、活用してみてください。
条件にあわせて返す結果を置き換える方法
ここまで、SQLで文字列を置き換える関数について紹介しました。
この章では、条件にあわせて返す結果を置き換える方法について見ていきましょう。
データベースのテーブル内には、数字で格納しているけど、検索して結果を取得する際には、文字列にして表示したい場合もあるでしょう。
そんな時に役立つのが、CASE式です。
CASE式は、「条件がAに当てはまればAルート、Bに当てはまればBルートを実行する」といった、条件分岐ができます。
SQLに限らず、さまざまな言語で使われる構文です。
では実際に、条件にあわせて返す結果を置き換えてみましょう。
次の社員情報を格納したテーブルを用意しました。
mysql> SELECT * FROM employee; +---------------+---------------+----------+ | employee_code | employee_name | division | +---------------+---------------+----------+ | 1 | Suzuki | 1 | | 2 | Hashimoto | 3 | | 3 | Taguchi | 1 | | 4 | Watanabe | 2 | | 5 | Satou | 0 | +---------------+---------------+----------+ 5 rows in set (0.00 sec)
このテーブルの divisionカラム の値を、文字列に置き換えて取得してみましょう。
次のように記述できます。
SELECT employee_code, employee_name, (CASE division WHEN 1 THEN '営業部' WHEN 2 THEN '開発部' WHEN 3 THEN '販促部' ELSE '不明' END) AS division FROM employee;
上記SQL文の、CASE式の部分だけを取り出して見ていきましょう。
CASE division WHEN 1 THEN '営業部' WHEN 2 THEN '開発部' WHEN 3 THEN '販促部' ELSE '不明' END
まず、CASEの後ろに対象のカラム名（ここでは divisionカラム）を記述します。
その後ろに「WHEN」を記述し、カラムの値を場合ごとに分けていきましょう。
ここでは、divisionカラムの値が「1」の場合は「営業部」、「2」の場合は「開発部」といったように指定しています。
そして「ELSE」の部分は、値がどれにも当てはまらない場合に、置き換える文字列を指定しました。
ここでは「不明」としています。
CASE式の最後に、「END」と記述すればOKです。
上記のSQL文を実行すると、次のように結果を取得できます。
mysql> SELECT employee_code, employee_name, (CASE division WHEN 1 THEN '営業部' WHEN 2 THEN '開発部' WHEN 3 THEN '販促部' ELSE '不明' END) AS division FROM employee; +---------------+---------------+-----------+ | employee_code | employee_name | division | +---------------+---------------+-----------+ | 1 | Suzuki | 営業部 | | 2 | Hashimoto | 販促部 | | 3 | Taguchi | 営業部 | | 4 | Watanabe | 開発部 | | 5 | Satou | 不明 | +---------------+---------------+-----------+ 5 rows in set (0.00 sec)
それぞれの値が、置き換えた文字列になっているのがわかりますね！
なお、CASE式の詳しい使い方は、以下の記事で解説しているので、ぜひ参考にしてみてください。
【関連記事】
▶︎【SQL】複雑な条件にも対応できるCASE式について解説。
NULL値を置き換えるには
ここまで、文字列の置き換え方法を紹介してきました。
カラムによっては、値に「NULL」が含まれている場合もあるでしょう。
NULL値を、別の値に一括で置き換えられたら便利ですよね。
ここでは、DBMSごとにNULL値を置き換える方法を見ていきましょう！
MySQLの場合
MySQLでNULL値を置き換える場合は、「IFNULL関数」か「COALESCE関数」を使いましょう。
次のように記述します。
SELECT IFNULL(カラム名,'置き換える文字列') FROM テーブル名; SELECT COALESCE(カラム名,'置き換える文字列') FROM テーブル名;
どちらも記述方法は同じです。
では、先ほど作成したテーブルに、NULL値の入ったデータを追加しましょう。
mysql> SELECT * FROM employee; +---------------+---------------+----------+ | employee_code | employee_name | division | +---------------+---------------+----------+ | 1 | Suzuki | 1 | | 2 | Hashimoto | 3 | | 3 | Taguchi | 1 | | 4 | Watanabe | 2 | | 5 | Satou | 0 | | 6 | NULL | 2 | +---------------+---------------+----------+ 6 rows in set (0.00 sec)
このテーブルの employee_nameカラム のNULL値を、別の文字列に置き換えて取得します。
SQL文の記述は次の通りです。
SELECT IFNULL(employee_name,'Morita') FROM employee;
上記SQL文を実行すると、次のように結果を取得できました。
mysql> SELECT IFNULL(employee_name,'Morita') FROM employee; +--------------------------------+ | IFNULL(employee_name,'Morita') | +--------------------------------+ | Suzuki | | Hashimoto | | Taguchi | | Watanabe | | Satou | | Morita | +--------------------------------+ 6 rows in set (0.01 sec)
NULL値が文字列に置き換わっています。
また、COALESCE関数を使っても、同様の結果を取得できます。
mysql> SELECT COALESCE(employee_name,'Morita') FROM employee; +----------------------------------+ | COALESCE(employee_name,'Morita') | +----------------------------------+ | Suzuki | | Hashimoto | | Taguchi | | Watanabe | | Satou | | Morita | +----------------------------------+ 6 rows in set (0.00 sec)
MySQLでNULL値を置き換えたい場合は、「IFNULL関数」か「COALESCE関数」を使うと便利です！
PostgreSQLの場合
PostgreSQLでNULL値を置き換える場合は、「COALESCE関数」を使いましょう。
使い方は、MySQLで紹介した通りです。
SELECT COALESCE(カラム名,'置き換える文字列') FROM テーブル名;
Oracleの場合
OracleでNULL値を置き換えたい場合は、「NVL関数」か「COALESCE関数」を使います。
COALESCE関数の使い方は、他のDBMSで説明した通りです。
NVL関数は、次のように記述します。
SELECT NVL(カラム名,'置き換える文字列') FROM dual;
記述方法は、他の関数と同じです。
執筆者がOracleを使用していないため、実行結果は省略します。
SQLServerの場合
SQLServerでNULL値を置き換える場合は、「IsNull関数」を使いましょう。
IsNull関数は、次のように記述します。
SELECT IsNull(カラム名, '置き換える文字列') FROM テーブル名;
IsNull関数も記述方法は、他の関数と同じです。
執筆者がSQLServerを使用していないため、実行結果は省略します。
置き換えてUPDATEする方法
最後に、データをアップロードする際に、値を置き換える方法を紹介します。
文字列を置き換えてアップデートする際は、REPLACE関数を使って次のように記述しましょう。
UPDATE テーブル名SET カラム名 = REPLACE(カラム名, '置換前の文字列', '置換後の文字列');
REPLACE関数の使い方は、先述した通りです。
実際に、データをアップデートしてみましょう。
UPDATE employee SET employee_name = REPLACE(employee_name, 'Satou', 'Abe');
上記SQL文を実行後、テーブルを確認すると次のように結果を取得できました。
mysql> SELECT * FROM employee; +---------------+---------------+----------+ | employee_code | employee_name | division | +---------------+---------------+----------+ | 1 | Suzuki | 1 | | 2 | Hashimoto | 3 | | 3 | Taguchi | 1 | | 4 | Watanabe | 2 | | 5 | Abe | 0 | | 6 | NULL | 2 | +---------------+---------------+----------+ 6 rows in set (0.00 sec)
ちゃんと、文字列が置き換えられてアップデートされています！
