sql抽取身份证信息生日，年龄，星座，属性，

提取内容：birthday,age,sex,property(属性),constellation(星座),province,age_group,six_idcard,census_register

 insert into test008
	select 
      cvb4.name
      ,cvb4.mobile
	  ,cvb4.idcard
	  ,cvb4.birthday
	  ,cvb4.age
	  ,cvb4.sex
	  ,cvb4.property
	  ,cvb4.constellation
	  ,cvb4.province
	  ,cvb4.hj
      ,cvb4.create_date	  
		from	
	(select 
	  name
		,mobile
		,idcard
		,create_date
		, case when length(idcard) = 18 then  
                      concat_ws('-',substr(idcard,7,4),substr(idcard,11,2),
                                substr(idcard,13,2)
                                ) 
      when length(idcard) = 15 then 
                     concat('19',substr(idcard,7,2),
                            '-', substr(idcard,9,2),
                            '-', substr(idcard,11,2)
                            ) 
      else null end  as birthday,
case when length(idcard) = 18 then
           floor(datediff( from_unixtime(unix_timestamp(),'%Y-%m-%d'), 
                      concat_ws('-',substr(idcard,7,4),substr(idcard,11,2),
                                substr(idcard,13,2)
                                ) 
                     )/365 )
      when length(idcard) = 15 then 
           floor(datediff( from_unixtime(unix_timestamp(),'%Y-%m-%d'),
                     concat('19',substr(idcard,7,2),
                            '-', substr(idcard,9,2),
                            '-', substr(idcard,11,2)
                            ) 
                    )/365 ) 
      else null end  as age,
  case when length(idcard) = 18 then
            case when substr(idcard,17,1)%2 = 0 then '女' 
                 when substr(idcard,17,1)%2 <> 0 then '男'
                 else null end
       when length(idcard) = 15 then 
            case when substr(idcard,15)%2 = 0 then '女' 
                 when substr(idcard,15)%2 <> 0 then '男'
                 else null end 
       else null end  as sex,
  case when length(idcard) = 18 then 
            case when substr(idcard,11,2)=01 then '鼠' 
                 when substr(idcard,11,2)=02 then '牛' 
	             when substr(idcard,11,2)=03 then '虎' 
	             when substr(idcard,11,2)=04 then '兔' 
	             when substr(idcard,11,2)=05 then '龙' 
	             when substr(idcard,11,2)=06 then '蛇' 
	             when substr(idcard,11,2)=07 then '马' 
	             when substr(idcard,11,2)=08 then '羊' 
	             when substr(idcard,11,2)=09 then '猴' 
	             when substr(idcard,11,2)=10 then '鸡' 
	             when substr(idcard,11,2)=11 then '狗' 
	             when substr(idcard,11,2)=12 then '猪' 
	             else null end 
	    else null end as property,
   case when length(idcard) = 18 then 
  case when substr(idcard,12,3)>=312 and substr(idcard,12,3)<=419 then '白羊座'
     when substr(idcard,12,3)>=420 and substr(idcard,12,3)<=520 then '金牛座'
	   when substr(idcard,12,3)>=521 and substr(idcard,12,3)<=621 then '双子座'
	   when substr(idcard,12,3)>=622 and substr(idcard,12,3)<=722 then '巨蟹座'
	   when substr(idcard,12,3)>=723 and substr(idcard,12,3)<=822 then '狮子座'
	   when substr(idcard,12,3)>=823 and substr(idcard,12,3)<=922 then '处女座'
	   when substr(idcard,12,3)>=923 and substr(idcard,12,3)<=930 then '天枰座'
	   when substr(idcard,11,4)>=1001 and substr(idcard,11,4)<=1023 then '天枰座'
	   when substr(idcard,11,4)>=1024 and substr(idcard,11,4)<=1121 then '天蝎座'
	   when substr(idcard,11,4)>=1122 and substr(idcard,11,4)<=1221 then '射手座'
	   when substr(idcard,11,4)>=1222 and substr(idcard,11,4)<=1231 then '魔蝎座'
	   when substr(idcard,12,3)>=101 and substr(idcard,12,3)<=119 then '魔蝎座'
	   when substr(idcard,12,3)>=120 and substr(idcard,12,3)<=218 then '水瓶座'
	   when substr(idcard,12,3)>=219 and substr(idcard,12,3)<320 then '双鱼座'
else null end 
else null end as  constellation,		
  case when length(idcard) = 18 then
            case when substr(idcard,1,2)=11 then '北京' 
             when substr(idcard,1,2)=12 then '天津'
             when substr(idcard,1,2)=13 then '河北'
             when substr(idcard,1,2)=14 then '山西'
             when substr(idcard,1,2)=15 then '内蒙古'
             when substr(idcard,1,2)=21 then '辽宁'
             when substr(idcard,1,2)=22 then '吉林'
             when substr(idcard,1,2)=23 then '黑龙江'
             when substr(idcard,1,2)=31 then '上海'
             when substr(idcard,1,2)=32 then '江苏'
             when substr(idcard,1,2)=33 then '浙江'
             when substr(idcard,1,2)=34 then '安徽'
             when substr(idcard,1,2)=35 then '福建'
             when substr(idcard,1,2)=36 then '江西'
             when substr(idcard,1,2)=37 then '山东'
             when substr(idcard,1,2)=41 then '河南'
             when substr(idcard,1,2)=42 then '湖北'
             when substr(idcard,1,2)=43 then '湖南'
             when substr(idcard,1,2)=44 then '广东'
             when substr(idcard,1,2)=45 then '广西'
             when substr(idcard,1,2)=46 then '海南'
             when substr(idcard,1,2)=50 then '重庆'
             when substr(idcard,1,2)=51 then '四川'
             when substr(idcard,1,2)=52 then '贵州'
             when substr(idcard,1,2)=53 then '云南'
             when substr(idcard,1,2)=54 then '西藏'
             when substr(idcard,1,2)=61 then '陕西'
             when substr(idcard,1,2)=62 then '甘肃'
             when substr(idcard,1,2)=63 then '青海'
             when substr(idcard,1,2)=64 then '宁夏'
             when substr(idcard,1,2)=65 then '新疆'
             when substr(idcard,1,2)=71 then '台湾'
             when substr(idcard,1,2)=81 then '香港'
             when substr(idcard,1,2)=82 then '澳门'
             when substr(idcard,1,2)=91 then '国外'
             else null end
      else null end as province,
   (case when length(idcard) = 18 then substr(idcard,1,6)
   else null end)hj	 
 from CITRN_VI_BAK_49) as cvb4