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

最新推荐文章于 2023-12-14 14:09:56 发布

码农致富

最新推荐文章于 2023-12-14 14:09:56 发布

阅读量2w

点赞数 3

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/u011781521/article/details/77993479

版权

Oracle 专栏收录该内容

3 篇文章

订阅专栏

本文介绍如何使用Oracle SQL语句从身份证号码中提取性别和年龄信息。包括根据身份证号的第17位判断性别，以及通过出生年份计算精确到日的年龄。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、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'