How to parse ID Card in mysql Database
This article will explain in detail how to parse ID cards in mysql database. Xiaobian thinks it is quite practical, so share it with you for reference. I hope you can gain something after reading this article.
mysql method of resolving ID card: first open the command window; then execute SQL statement "select case left(id_number,2)when '11' then 'Beijing City'... from t_users limit 10"to get user attributes.
mysql obtains user attribute information according to ID card information
Required: Obtain user attributes according to ID card information
Method: You can use the following SQL statement:
select case left (id_number, 2) when '11' then 'Beijing City'12' then ' Tianjin City '13' then 'Hebei Province'14' then ' Shanxi Province '15' then 'Inner Mongolia Inner Mongolia Autonomous Region when '21' then ' Liaoning 'Liaoning Province' 22' then ' Jilin Province when '23' then 'Heilongjiang' when '31' then 'Shanghai'when '32' then 'Jiangsu Province'when '33' then 'Zhejiang Province'when '34' then 'Anhui Province'when '35' then 'Fujian Province'when '36' then 'Jiangxi Province'when '37' then 'Shandong Province'when '41' then 'Henan Province' when '42' then 'Hubei Province'when '43' then 'Guangdong Province' when '45' then 'Guangxi Zhuang Autonomous Region'when'46' then 'Hainan Province'when '50' then 'Chongqing City'when '51' then 'Sichuan Province'when '52' then 'Guizhou Province'when '53' then 'Yunnan Province'when '54' then 'Tibet Autonomous Region'when '61' then 'Shaanxi Province' when '62' then 'Gansu Province' when '63' then 'Qinghai Province' when '64' then 'Ningxia Hui Autonomous Region' when '65' then'Xinjiang Uygur Autonomous Region'when '71' then 'Taiwan Province'when '81' then 'Hong Kong Special Administrative Region'when '82' then 'Macao Special Administrative Region'else 'Unknown' end as Province,year (curdate())-if (length(id_number)=18,substring (id_number,7,4),if (length(id_number)=15,concat ('19 ',substring(id_number,7,2)),null)) as Age, case if (length(id_number)=18, cast (substring(id_number,17,1) as UNSIGNED)%2, if (length(id_number)=15,cast(substring(id_number,15,1) as UNSIGNED)%2,3)) when 1 then 'male'when 0 then 'female'else 'unknown'end as gender from t_users limit 10
The results were as follows:
+--------------------------+--------+--------+| provinces | age | gender |+--------------------------+--------+--------+| Guangdong Province | 40 |male || Xinjiang Uygur Autonomous Region | 25 |male || Guangdong Province | 22 |male || Zhejiang Province | 24 |female || Guangdong Province | 38 |male || unknown | NULL |unknown || Henan Province | 25 |male || Guangdong Province | 25 |female || Jiangxi Province | 37 |female || Guangdong Province | 28 |female |+-------------------------+----------+10 rows in set (0.00 sec) About mysql database how to parse ID card to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.
