SQLでの文字列置換についてまとめています。
MySQLのサンプルデータベースEmployeesを使用しています。
SQLの文字列置換は、replaceを使用
SQLで文字列の一部を置換するには、replaceを使います。※MySQLの場合
第一引数に対象文字列、第二引数に置換前文字列、第三引数に置換後文字列を指定します。なお、第三引数を省略すると、置換対象部分が削除されます。
以下は、dog and catのdogをmouseに置換するサンプルコードです。
mysql> select replace('dog and cat', 'dog', 'mouse' );
+-----------------------------------------+
| replace('dog and cat', 'dog', 'mouse' ) |
+-----------------------------------------+
| mouse and cat |
+-----------------------------------------+
1 row in set (0.00 sec)
複数文字列の置換は、replaceを入れ子にする
複数の文字列を同時に置換するには、replaceを入れ子構造にすることで実現できます。
以下は、dog and catのdog→mouse、cat→nekoに置換するサンプルコードです。dog and catという文字列が、mouse and nekoに置換されました。
mysql> select replace(replace('dog and cat', 'dog', 'mouse' ), 'cat', 'neko');
+-----------------------------------------------------------------+
| replace(replace('dog and cat', 'dog', 'mouse' ), 'cat', 'neko') |
+-----------------------------------------------------------------+
| mouse and neko |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
【関連記事】
▶SQLのREPLACE関数を用いた置換機能 使用方法を徹底的に解説
複数文字列の置換は、case式を組み合わせると簡潔に記述できる
入れ子にする方法は２つくらいなら良いですが、さらに多くのキーワードを複数同時置換する場合に、コードが読みづらくなります。
case式とreplaceを組み合わせると、置換対象のキーワードが増えてもスッキリ記述できます。
こちらは、employees（社員）テーブルから、first_name（姓名の名）、last_name（姓名の姓）、gender（性別）を取得するSQLコード。
mysql> select first_name, last_name, gender from employees limit 10; +------------+-----------+--------+ | first_name | last_name | gender | +------------+-----------+--------+ | Georgi | Facello | M | | Bezalel | Simmel | F | | Parto | Bamford | M | | Chirstian | Koblick | M | | Kyoichi | Maliniak | M | | Anneke | Preusig | F | | Tzvetan | Zielinski | F | | Saniya | Kalloufi | M | | Sumant | Peac | F | | Duangkaew | Piveteau | F | +------------+-----------+--------+ 10 rows in set (0.01 sec)
上記のSQLを少し修正してみましょう。
下記のように、case式のwhenで対象カラムにパターンマッチをおこない、replace関数を実行します。今回は、genderがMならDansei、FならJoseiと文字列を置換しています。
今回のケースでは、対象が一文字なのでlike ‘%M%’とワイルドカード指定はしなくても良いのですが、汎用性を考えてこのようにしました。
mysql> select first_name, last_name,
-> case
-> when gender like '%M%' then replace(gender, 'M', 'Dansei')
-> when gender like '%F%' then replace(gender, 'F', 'Josei')
-> end as seibetu
-> from employees limit 10;
+------------+-----------+---------+
| first_name | last_name | seibetu |
+------------+-----------+---------+
| xGeorgi | Facello | Dansei |
| Bezalel | Simmel | Josei |
| Parto | Bamford | Dansei |
| Chirstian | Koblick | Dansei |
| Kyoichi | Maliniak | Dansei |
| Anneke | Preusig | Josei |
| Tzvetan | Zielinski | Josei |
| Saniya | Kalloufi | Dansei |
| Sumant | Peac | Josei |
| Duangkaew | Piveteau | Josei |
+------------+-----------+---------+
10 rows in set (0.00 sec)
【関連記事】
▶SQLのCASE式サンプル集 order byやgroup byとの組み合わせもバッチリ
なお、Oracle、SQL Server、PostgreSQLともreplace関数が使用可能です。
まとめ
- 文字列の置換をおこなうには、replace関数を使う
- 複数の文字列置換を同時におこなうには、replace関数を入れ子にする
- 複数の文字列置換で、置換対象の数が多い場合は、case式と組み合わせると簡潔に記述可能。
