置換 – 東京発、雲に乗って

私が開発する場合、氏名などのカナは全角カタカナでバリデーションチェックを行いますが、
とくにチェックされてなかったシステムがあって、
人によってひらがなだったりカタカナだったりばらばらでした。

そのシステムの改修で
カタカナ以外エラーになるようにバリデーションチェックを追加したのですが
それだけではなく、既存のデータをカタカナに統一する必要があります。

スマートな方法がないか調べましたが、結局やったのはこちら。

UPDATE `テーブル名` SET column = REPLACE(column, "が","ガ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぎ","ギ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぐ","グ");
UPDATE `テーブル名` SET column = REPLACE(column, "げ","ゲ");
UPDATE `テーブル名` SET column = REPLACE(column, "ご","ゴ");
UPDATE `テーブル名` SET column = REPLACE(column, "ざ","ザ");
UPDATE `テーブル名` SET column = REPLACE(column, "じ","ジ");
UPDATE `テーブル名` SET column = REPLACE(column, "ず","ズ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぜ","ゼ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぞ","ゾ");
UPDATE `テーブル名` SET column = REPLACE(column, "ば","バ");
UPDATE `テーブル名` SET column = REPLACE(column, "び","ビ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぶ","ブ");
UPDATE `テーブル名` SET column = REPLACE(column, "べ","ベ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぼ","ボ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぱ","パ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぴ","ピ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぷ","プ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぺ","ペ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぽ","ポ");

UPDATE `テーブル名` SET column = REPLACE(column, "あ","ア");
UPDATE `テーブル名` SET column = REPLACE(column, "い","イ");
UPDATE `テーブル名` SET column = REPLACE(column, "う","ウ");
UPDATE `テーブル名` SET column = REPLACE(column, "え","エ");
UPDATE `テーブル名` SET column = REPLACE(column, "お","オ");
UPDATE `テーブル名` SET column = REPLACE(column, "か","カ");
UPDATE `テーブル名` SET column = REPLACE(column, "き","キ");
UPDATE `テーブル名` SET column = REPLACE(column, "く","ク");
UPDATE `テーブル名` SET column = REPLACE(column, "け","ケ");
UPDATE `テーブル名` SET column = REPLACE(column, "こ","コ");
UPDATE `テーブル名` SET column = REPLACE(column, "さ","サ");
UPDATE `テーブル名` SET column = REPLACE(column, "し","シ");
UPDATE `テーブル名` SET column = REPLACE(column, "す","ス");
UPDATE `テーブル名` SET column = REPLACE(column, "せ","セ");
UPDATE `テーブル名` SET column = REPLACE(column, "そ","ソ");
UPDATE `テーブル名` SET column = REPLACE(column, "た","タ");
UPDATE `テーブル名` SET column = REPLACE(column, "ち","チ");
UPDATE `テーブル名` SET column = REPLACE(column, "つ","ツ");
UPDATE `テーブル名` SET column = REPLACE(column, "て","テ");
UPDATE `テーブル名` SET column = REPLACE(column, "と","ト");
UPDATE `テーブル名` SET column = REPLACE(column, "な","ナ");
UPDATE `テーブル名` SET column = REPLACE(column, "に","ニ");
UPDATE `テーブル名` SET column = REPLACE(column, "ぬ","ヌ");
UPDATE `テーブル名` SET column = REPLACE(column, "ね","ネ");
UPDATE `テーブル名` SET column = REPLACE(column, "の","ノ");
UPDATE `テーブル名` SET column = REPLACE(column, "は","ハ");
UPDATE `テーブル名` SET column = REPLACE(column, "ひ","ヒ");
UPDATE `テーブル名` SET column = REPLACE(column, "ふ","フ");
UPDATE `テーブル名` SET column = REPLACE(column, "へ","ヘ");
UPDATE `テーブル名` SET column = REPLACE(column, "ほ","ホ");
UPDATE `テーブル名` SET column = REPLACE(column, "ま","マ");
UPDATE `テーブル名` SET column = REPLACE(column, "み","ミ");
UPDATE `テーブル名` SET column = REPLACE(column, "む","ム");
UPDATE `テーブル名` SET column = REPLACE(column, "め","メ");
UPDATE `テーブル名` SET column = REPLACE(column, "も","モ");
UPDATE `テーブル名` SET column = REPLACE(column, "や","ヤ");
UPDATE `テーブル名` SET column = REPLACE(column, "ゆ","ユ");
UPDATE `テーブル名` SET column = REPLACE(column, "よ","ヨ");
UPDATE `テーブル名` SET column = REPLACE(column, "ら","ラ");
UPDATE `テーブル名` SET column = REPLACE(column, "り","リ");
UPDATE `テーブル名` SET column = REPLACE(column, "る","ル");
UPDATE `テーブル名` SET column = REPLACE(column, "れ","レ");
UPDATE `テーブル名` SET column = REPLACE(column, "ろ","ロ");
UPDATE `テーブル名` SET column = REPLACE(column, "わ","ワ");
UPDATE `テーブル名` SET column = REPLACE(column, "を","ヲ");
UPDATE `テーブル名` SET column = REPLACE(column, "ん","ン");

UPDATE `テーブル名` SET column = REPLACE(column, "が","ガ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぎ","ギ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぐ","グ");

UPDATE `テーブル名` SET column = REPLACE(column, "げ","ゲ");

UPDATE `テーブル名` SET column = REPLACE(column, "ご","ゴ");

UPDATE `テーブル名` SET column = REPLACE(column, "ざ","ザ");

UPDATE `テーブル名` SET column = REPLACE(column, "じ","ジ");

UPDATE `テーブル名` SET column = REPLACE(column, "ず","ズ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぜ","ゼ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぞ","ゾ");

UPDATE `テーブル名` SET column = REPLACE(column, "ば","バ");

UPDATE `テーブル名` SET column = REPLACE(column, "び","ビ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぶ","ブ");

UPDATE `テーブル名` SET column = REPLACE(column, "べ","ベ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぼ","ボ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぱ","パ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぴ","ピ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぷ","プ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぺ","ペ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぽ","ポ");

UPDATE `テーブル名` SET column = REPLACE(column, "あ","ア");

UPDATE `テーブル名` SET column = REPLACE(column, "い","イ");

UPDATE `テーブル名` SET column = REPLACE(column, "う","ウ");

UPDATE `テーブル名` SET column = REPLACE(column, "え","エ");

UPDATE `テーブル名` SET column = REPLACE(column, "お","オ");

UPDATE `テーブル名` SET column = REPLACE(column, "か","カ");

UPDATE `テーブル名` SET column = REPLACE(column, "き","キ");

UPDATE `テーブル名` SET column = REPLACE(column, "く","ク");

UPDATE `テーブル名` SET column = REPLACE(column, "け","ケ");

UPDATE `テーブル名` SET column = REPLACE(column, "こ","コ");

UPDATE `テーブル名` SET column = REPLACE(column, "さ","サ");

UPDATE `テーブル名` SET column = REPLACE(column, "し","シ");

UPDATE `テーブル名` SET column = REPLACE(column, "す","ス");

UPDATE `テーブル名` SET column = REPLACE(column, "せ","セ");

UPDATE `テーブル名` SET column = REPLACE(column, "そ","ソ");

UPDATE `テーブル名` SET column = REPLACE(column, "た","タ");

UPDATE `テーブル名` SET column = REPLACE(column, "ち","チ");

UPDATE `テーブル名` SET column = REPLACE(column, "つ","ツ");

UPDATE `テーブル名` SET column = REPLACE(column, "て","テ");

UPDATE `テーブル名` SET column = REPLACE(column, "と","ト");

UPDATE `テーブル名` SET column = REPLACE(column, "な","ナ");

UPDATE `テーブル名` SET column = REPLACE(column, "に","ニ");

UPDATE `テーブル名` SET column = REPLACE(column, "ぬ","ヌ");

UPDATE `テーブル名` SET column = REPLACE(column, "ね","ネ");

UPDATE `テーブル名` SET column = REPLACE(column, "の","ノ");

UPDATE `テーブル名` SET column = REPLACE(column, "は","ハ");

UPDATE `テーブル名` SET column = REPLACE(column, "ひ","ヒ");

UPDATE `テーブル名` SET column = REPLACE(column, "ふ","フ");

UPDATE `テーブル名` SET column = REPLACE(column, "へ","ヘ");

UPDATE `テーブル名` SET column = REPLACE(column, "ほ","ホ");

UPDATE `テーブル名` SET column = REPLACE(column, "ま","マ");

UPDATE `テーブル名` SET column = REPLACE(column, "み","ミ");

UPDATE `テーブル名` SET column = REPLACE(column, "む","ム");

UPDATE `テーブル名` SET column = REPLACE(column, "め","メ");

UPDATE `テーブル名` SET column = REPLACE(column, "も","モ");

UPDATE `テーブル名` SET column = REPLACE(column, "や","ヤ");

UPDATE `テーブル名` SET column = REPLACE(column, "ゆ","ユ");

UPDATE `テーブル名` SET column = REPLACE(column, "よ","ヨ");

UPDATE `テーブル名` SET column = REPLACE(column, "ら","ラ");

UPDATE `テーブル名` SET column = REPLACE(column, "り","リ");

UPDATE `テーブル名` SET column = REPLACE(column, "る","ル");

UPDATE `テーブル名` SET column = REPLACE(column, "れ","レ");

UPDATE `テーブル名` SET column = REPLACE(column, "ろ","ロ");

UPDATE `テーブル名` SET column = REPLACE(column, "わ","ワ");

UPDATE `テーブル名` SET column = REPLACE(column, "を","ヲ");

UPDATE `テーブル名` SET column = REPLACE(column, "ん","ン");

実にわかりやすい方法でした。

viでのテキストの置換はマウスを使わずにできるため、慣れると非常に素早く行うことができます。
しかし、しばらく使っていないと「あれ、置換の書式どうだっけ？」となりませんか？
viのコマンドは履歴が追えるので履歴を見てなんとかなったりするのですが、
念のため忘れないように残しておきます。

まずは基本。今いる行だけの置換

:s/置換前/置換後/g

1	:s/置換前/置換後/g

全行の置換

:%s/置換前/置換後/g

1	:%s/置換前/置換後/g

cオプションをつけると確認しながら置換

:%s/置換前/置換後/gc

1	:%s/置換前/置換後/gc

置換文字列に「/」が入る場合は区切り文字を適当に変えます。

:%s;/home/user1;/var/www/html;g

1	:%s;/home/user1;/var/www/html;g

タグ: 置換

【SQL】DBのひらがなをカタカナに一括置換する

【MySQL】文字列の一部を一括置換する

【Linux】viでつい忘れる置換の書式

【Linux】複数のファイルを一括置換する