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

来源：互联网发布：ubuntu 17配置lamp 编辑：程序博客网时间：2024/05/01 09:54

一、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位身份证获取年龄性别

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         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, 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'

阅读全文

0 0