Updated:
テクニカルブログ

SQLで文字列を置き換える方法（MySQL）

背景

都道府県のIDをカンマ区切りで持つカラムがあり（それ自体がどうなのか？という観点は置いておいて）、IDではなく都道府県名を表示してほしいという依頼がありました。

都道府県IDを都道府県名に置き換えるために文字列を置換する関数を探してみました。

文字列の置換

MySQLにはreplaceという関数があります。
https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_replace

replaceの引数

replaceは

1
replace(置換対象の文字列, 置換する文字列, 置換後の文字列)

となっています。

公式に書いてある例を手元で実行してみます。

1
2
3
4
5
6
7
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
+-------------------------------------+
| REPLACE('www.mysql.com', 'w', 'Ww') |
+-------------------------------------+
| WwWwWw.mysql.com                    |
+-------------------------------------+
1 row in set (0.00 sec)

wは複数回現れますが、それを全て置換していることがわかります。

複数の文字列を置換するには

今回置換したい文字列は都道府県IDそれぞれを都道府県名に置換したいので、最大で47回置換する必要があります。

例えば以下の例は

1
2
3
4
5
6
7
mysql> select '1,11,12,13,14' as prefecture_ids;
+----------------+
| prefecture_ids |
+----------------+
| 1,11,12,13,14  |
+----------------+
1 row in set (0.00 sec)

置換後このようになっている必要があります。

1
北海道,埼玉県,千葉県,東京都,神奈川県

置換後の文字列を再度置換することで、実現できます。

1
2
3
4
5
6
7
mysql> select replace(replace(replace(replace(replace('1,11,12,13,14', '14', '神奈川県'), '13', '東京都'), '12', '千葉県'), '11', '埼玉県'), '1', '北海道') as prefecture_names;
+------------------------------------------------------+
| prefecture_names                                     |
+------------------------------------------------------+
| 北海道,埼玉県,千葉県,東京都,神奈川県                 |
+------------------------------------------------------+
1 row in set (0.00 sec)

47都道府県を行うのは大変ですが、見えやすいように入れ子で書けるといいですね。

対象文字列に置換文字列が複数現れる場合は全て変換されてしまうので、重複しないよう大きな数字から変換する必要があります。北海道の変換よりも埼玉県の変換を行わないと、埼玉県となるところが北海道北海道となってしまいます。

まとめ

今回はreplace関数を利用しました。マスターデータがシンプルな場合で、joinしづらいデータに関してはクエリで置換する処理を書いてもよいと思います。

参考図書