SQL依据身份证号判断所在省份

UPDATE test set province =

CASE

when SUBSTRING(id_card,1,2)='11' then '北京'

when SUBSTRING(id_card,1,2)='12' then '天津'

when SUBSTRING(id_card,1,2)='13' then '河北'

when SUBSTRING(id_card,1,2)='14' then '山西'

when SUBSTRING(id_card,1,2)='15' then '内蒙古'

when SUBSTRING(id_card,1,2)='21' then '辽宁'

when SUBSTRING(id_card,1,2)='22' then '吉林'

when SUBSTRING(id_card,1,2)='23' then '黑龙江'

when SUBSTRING(id_card,1,2)='31' then '上海'

when SUBSTRING(id_card,1,2)='32' then '江苏'

when SUBSTRING(id_card,1,2)='33' then '浙江'

when SUBSTRING(id_card,1,2)='34' then '安徽'

when SUBSTRING(id_card,1,2)='35' then '福建'

when SUBSTRING(id_card,1,2)='36' then '江西'

when SUBSTRING(id_card,1,2)='37' then '山东'

when SUBSTRING(id_card,1,2)='41' then '河南'

when SUBSTRING(id_card,1,2)='42' then '湖北'

when SUBSTRING(id_card,1,2)='43' then '湖南'

when SUBSTRING(id_card,1,2)='44' then '广东'

when SUBSTRING(id_card,1,2)='45' then '广西'

when SUBSTRING(id_card,1,2)='46' then '海南'

when SUBSTRING(id_card,1,2)='50' then '重庆'

when SUBSTRING(id_card,1,2)='51' then '四川'

when SUBSTRING(id_card,1,2)='52' then '贵州'

when SUBSTRING(id_card,1,2)='53' then '云南'

when SUBSTRING(id_card,1,2)='54' then '西藏'

when SUBSTRING(id_card,1,2)='61' then '陕西'

when SUBSTRING(id_card,1,2)='62' then '甘肃'

when SUBSTRING(id_card,1,2)='63' then '青海'

when SUBSTRING(id_card,1,2)='64' then '宁夏'

when SUBSTRING(id_card,1,2)='65' then '新疆'

when SUBSTRING(id_card,1,2)='71' then '台湾'

when SUBSTRING(id_card,1,2)='81' then '香港'

when SUBSTRING(id_card,1,2)='91' then '澳门'

END