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

超、凢脫俗 2022-06-08 05:15 1642阅读 0赞

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

女生身份证:

431382198103246985

男生身份证:

150921197208173492

SQL语句如下:

select decode(mod (to_number(substr('110228197802199547' ,17, 1)),2 ),0, '女','男' ) as sex from dual ;
    select decode(mod (to_number(substr('530323197503252610' ,17, 1)),2 ),0, '女','男' ) as sex from dual ;
    
    select decode(mod (to_number(substr('110228197802199547' ,17, 1)),2 ),0, '2','1' ) as sex from dual ;
    select decode(mod (to_number(substr('530323197503252610' ,17, 1)),2 ),0, '2','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',7,8),'yyyy-MM-dd'),'yyyyMMdd'))/10000)  from dual;

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

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

select
            case
                when length(idcard) = 18 then
                       ceil((to_char(sysdate, 'yyyyMMdd') -
                              to_char(to_date(substr(idcard, 7, 8), 'yyyy-MM-dd'),
                                       'yyyyMMdd')) / 10000)
                when length(idcard) = 15 then
                         ceil((to_char(sysdate, 'yyyyMMdd') -
                              to_char(to_date('19' || substr(idcard, 7, 6),
                                               'yyyy-MM-dd'),
                                       'yyyyMMdd')) / 10000)
                     end as age,
            case
               when length(idcard) = 18 then
                         decode(mod(to_number(substr(idcard, 17, 1)), 2),
                                0,'2','1')
               when length(idcard) = 15 then
                         decode(mod(to_number(idcard), 2), 0, '2', '1')
                      end as sex,
            case
                     when length(idcard)=18 then
    	              to_char(to_date(substr(idcard, 7, 8), 'yyyy-MM-dd'),'yyyy-MM-dd')
               when  length(idcard)= 15 then
    								 to_char(to_date('19'||substr(idcard, 7,6), 'yyyy-MM-dd'),'yyyy-MM-dd')
                           end as birthday,id as mobile,name,guid
    
                 from usr where guid='174BD3D5879C3BF0E050007F010077DC'