Oracle根据身份证号码判断性别,年龄

一、Oracle根据身份证判断性别:

女生身份证:

431382198103246985

男生身份证:

150921197208173492

SQL语句如下:

select decode(mod (to_number(substr('110228197802199547',17,1)),2 ),'女','男' ) as sex from dual ;
select decode(mod (to_number(substr('530323197503252610','男' ) as sex from dual ;

select decode(mod (to_number(substr('110228197802199547','2','1' ) as sex from dual ;
select decode(mod (to_number(substr('530323197503252610','1' ) as sex from dual ;

二、Oracle根据身份证判断年龄:

方法一

select (to_char(sysdate,'yyyy') - substr('430426199303014475',7,4)) age from dual;

select trunc(to_number(to_char(sysdate,'yyyymmdd'))/10000 - to_number(to_char(to_date('1982-8-28','yyyy-mm-dd'),'yyyymmdd'))/10000) from dual

select trunc((to_char(sysdate,'yyyyMMdd')-to_char(to_date(substr('430426199303014475',8),'yyyy-MM-dd'),'yyyyMMdd'))/10000)  from dual;

三、根据15、18位身份证获取年龄性别

select 
    case
        when length(idcard) = 18 then
               ceil((to_char(sysdate,'yyyyMMdd') -
                      to_char(to_date(substr(idcard,'yyyyMMdd')) / 10000)
        when length(idcard) = 15 then
                 ceil((to_char(sysdate,'yyyyMMdd') -
                      to_char(to_date('19' || substr(idcard,6),'yyyyMMdd')) / 10000)
             end as age,case
       when length(idcard) = 18 then
                 decode(mod(to_number(substr(idcard,2),'1')
       when length(idcard) = 15 then
                 decode(mod(to_number(idcard),'1')
              end as sex
         from usr where guid='230E20A6FFA1B41CE050AE0AC684959F'

上面获取的格式是"1990" 只包含了年没有包含出生日期，如果要包含出生日期"1990-01-12"，就得使用如下格式:

    select
        case
            when length(idcard) = 18 then
                   ceil((to_char(sysdate,'yyyyMMdd') -
                          to_char(to_date(substr(idcard,'yyyyMMdd')) / 10000)
            when length(idcard) = 15 then
                     ceil((to_char(sysdate,'yyyyMMdd') -
                          to_char(to_date('19' || substr(idcard,'yyyyMMdd')) / 10000)
                 end as age,case
           when length(idcard) = 18 then
                     decode(mod(to_number(substr(idcard,'1')
           when length(idcard) = 15 then
                     decode(mod(to_number(idcard),'1')
                  end as sex,case
                 when length(idcard)=18 then
	              to_char(to_date(substr(idcard,'yyyy-MM-dd')
           when  length(idcard)= 15 then
								 to_char(to_date('19'||substr(idcard,'yyyy-MM-dd')
                       end as birthday,id as mobile,name,guid

             from usr where guid='174BD3D5879C3BF0E050007F010077DC'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!