舊系統裡的address 轉到新的5都的 SQL

舊系統裡的address 轉到新的5都的 SQL

使用SQL更換方法，將table改成你的table名稱及address改為你的欄位名稱
由於現在起“台”要換成“臺”，所以我們先將“台”換成“臺”。
update table set address = replace(address, '台', '臺');

update table set address = replace(address, '臺北縣', '新北市');
update table set address = replace(address, '臺中縣', '臺中市');
update table set address = replace(address, '臺南縣', '臺南市');
update table set address = replace(address, '高雄縣', '高雄市');


村改為「里」：
先檢查路名有沒有村字，以免掩蓋錯誤！排除過後，再進行更換。
select address from table where `address` like '%村%' and `address` like '%新北市%';
update table set address = replace(address, '村', '里') where `address` like '%村%' and `address` like '%新北市%';
select address from table where `address` like '%村%' and `address` like '%臺中市%';
update table set address = replace(address, '村', '里') where `address` like '%村%' and `address` like '%臺中市%';
select address from table where `address` like '%村%' and `address` like '%臺南市%';
update table set address = replace(address, '村', '里') where `address` like '%村%' and `address` like '%臺南市%';
select address from table where `address` like '%村%' and `address` like '%高雄市%';
update table set address = replace(address, '村', '里') where `address` like '%村%' and `address` like '%高雄市%';


臺北縣：
update table set address = replace(address, '萬里鄉', '萬里區');
update table set address = replace(address, '金山鄉', '金山區');
update table set address = replace(address, '板橋市', '板橋區');
update table set address = replace(address, '汐止市', '汐止區');
update table set address = replace(address, '深坑鄉', '深坑區');
update table set address = replace(address, '石碇鄉', '石碇區');
update table set address = replace(address, '瑞芳鎮', '瑞芳區');
update table set address = replace(address, '平溪鄉', '平溪區');
update table set address = replace(address, '雙溪鄉', '雙溪區');
update table set address = replace(address, '貢寮鄉', '貢寮區');
update table set address = replace(address, '新店市', '新店區');
update table set address = replace(address, '坪林鄉', '坪林區');
update table set address = replace(address, '烏來鄉', '烏來區');
update table set address = replace(address, '永和市', '永和區');
update table set address = replace(address, '中和市', '中和區');
update table set address = replace(address, '土城市', '土城區');
update table set address = replace(address, '三峽鎮', '三峽區');
update table set address = replace(address, '樹林市', '樹林區');
update table set address = replace(address, '鶯歌鎮', '鶯歌區');
update table set address = replace(address, '三重市', '三重區');
update table set address = replace(address, '新莊市', '新莊區');
update table set address = replace(address, '泰山鄉', '泰山區');
update table set address = replace(address, '林口鄉', '林口區');
update table set address = replace(address, '蘆洲市', '蘆洲區');
update table set address = replace(address, '五股鄉', '五股區');
update table set address = replace(address, '八里鄉', '八里區');
update table set address = replace(address, '淡水鎮', '淡水區');
update table set address = replace(address, '三芝鄉', '三芝區');
update table set address = replace(address, '石門鄉', '石門區');


臺中縣：
update table set address = replace(address, '太平市', '太平區');
update table set address = replace(address, '大里市', '大里區');
update table set address = replace(address, '霧峰鄉', '霧峰區');
update table set address = replace(address, '烏日鄉', '烏日區');
update table set address = replace(address, '豐原市', '豐原區');
update table set address = replace(address, '后里鄉', '后里區');
update table set address = replace(address, '石岡鄉', '石岡區');
update table set address = replace(address, '東勢鎮', '東勢區');
update table set address = replace(address, '和平鄉', '和平區');
update table set address = replace(address, '新社鄉', '新社區');
update table set address = replace(address, '潭子鄉', '潭子區');
update table set address = replace(address, '大雅鄉', '大雅區');
update table set address = replace(address, '神岡鄉', '神岡區');
update table set address = replace(address, '大肚鄉', '大肚區');
update table set address = replace(address, '沙鹿鎮', '沙鹿區');
update table set address = replace(address, '龍井鄉', '龍井區');
update table set address = replace(address, '梧棲鎮', '梧棲區');
update table set address = replace(address, '清水鎮', '清水區');
update table set address = replace(address, '大甲鎮', '大甲區');
update table set address = replace(address, '外埔鄉', '外埔區');
update table set address = replace(address, '大安鄉', '大安區');


臺南縣：
update table set address = replace(address, '永康市', '永康區');
update table set address = replace(address, '歸仁鄉', '歸仁區');
update table set address = replace(address, '新化鎮', '新化區');
update table set address = replace(address, '左鎮鄉', '左鎮區');
update table set address = replace(address, '玉井鄉', '玉井區');
update table set address = replace(address, '楠西鄉', '楠西區');
update table set address = replace(address, '南化鄉', '南化區');
update table set address = replace(address, '仁德鄉', '仁德區');
update table set address = replace(address, '關廟鄉', '關廟區');
update table set address = replace(address, '龍崎鄉', '龍崎區');
update table set address = replace(address, '官田鄉', '官田區');
update table set address = replace(address, '麻豆鎮', '麻豆區');
update table set address = replace(address, '佳里鎮', '佳里區');
update table set address = replace(address, '西港鄉', '西港區');
update table set address = replace(address, '七股鄉', '七股區');
update table set address = replace(address, '將軍鄉', '將軍區');
update table set address = replace(address, '學甲鎮', '學甲區');
update table set address = replace(address, '北門鄉', '北門區');
update table set address = replace(address, '新營市', '新營區');
update table set address = replace(address, '後壁鄉', '後壁區');
update table set address = replace(address, '白河鎮', '白河區');
update table set address = replace(address, '東山鄉', '東山區');
update table set address = replace(address, '六甲鄉', '六甲區');
update table set address = replace(address, '下營鄉', '下營區');
update table set address = replace(address, '柳營鄉', '柳營區');
update table set address = replace(address, '鹽水鎮', '鹽水區');
update table set address = replace(address, '善化鎮', '善化區');
update table set address = replace(address, '大內鄉', '大內區');
update table set address = replace(address, '山上鄉', '山上區');
update table set address = replace(address, '新市鄉', '新市區');
update table set address = replace(address, '安定鄉', '安定區');


高雄縣：
update table set address = replace(address, '仁武鄉', '仁武區');
update table set address = replace(address, '大社鄉', '大社區');
update table set address = replace(address, '岡山鎮', '岡山區');
update table set address = replace(address, '路竹鄉', '路竹區');
update table set address = replace(address, '阿蓮鄉', '阿蓮區');
update table set address = replace(address, '田寮鄉', '田寮區');
update table set address = replace(address, '燕巢鄉', '燕巢區');
update table set address = replace(address, '橋頭鄉', '橋頭區');
update table set address = replace(address, '梓官鄉', '梓官區');
update table set address = replace(address, '彌陀鄉', '彌陀區');
update table set address = replace(address, '永安鄉', '永安區');
update table set address = replace(address, '湖內鄉', '湖內區');
update table set address = replace(address, '鳳山市', '鳳山區');
update table set address = replace(address, '大寮鄉', '大寮區');
update table set address = replace(address, '林園鄉', '林園區');
update table set address = replace(address, '鳥松鄉', '鳥松區');
update table set address = replace(address, '大樹鄉', '大樹區');
update table set address = replace(address, '旗山鎮', '旗山區');
update table set address = replace(address, '美濃鎮', '美濃區');
update table set address = replace(address, '六龜鄉', '六龜區');
update table set address = replace(address, '內門鄉', '內門區');
update table set address = replace(address, '杉林鄉', '杉林區');
update table set address = replace(address, '甲仙鄉', '甲仙區');
update table set address = replace(address, '桃源鄉', '桃源區');
update table set address = replace(address, '茂林鄉', '茂林區');
update table set address = replace(address, '茄萣鄉', '茄萣區');
update table set address = replace(address, '那瑪夏鄉', '那瑪夏區');


資料來源: http://www.aoron.com/%E6%96%B0%E4%BA%94%E9%83%BD%E5%9C%B0%E5%9D%80%E5%90%8D%E7%A8%B1%E6%9B%B4%E6%8F%9B%E5%88%97%E8%A1%A8.php