2019年2月 – 東京発、雲に乗って

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

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

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

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, "ん","ン");

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

月: 2019年2月

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