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

公众号_码农致富
1,188 阅读7分钟
原文链接： blog.csdn.net

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

女生身份证:

[html] view plain copy print?
  1. 431382198103246985   
431382198103246985

男生身份证:

[html] view plain copy print?
  1. 150921197208173492   
150921197208173492

SQL语句如下:

[html] view plain copy print?
  1. select decode(mod (to_number(substr('110228197802199547' ,17, 1)),2 ),0, '女','男' ) as sex from dual ;  
  2. select decode(mod (to_number(substr('530323197503252610' ,17, 1)),2 ),0, '女','男' ) as sex from dual ;  
  3.   
  4. select decode(mod (to_number(substr('110228197802199547' ,17, 1)),2 ),0, '2','1' ) as sex from dual ;  
  5. select decode(mod (to_number(substr('530323197503252610' ,17, 1)),2 ),0, '2','1' ) as sex from dual ;  
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根据身份证判断年龄:


方法一

[html] view plain copy print?
  1. select (to_char(sysdate, 'yyyy') - substr('430426199303014475', 7, 4)) age from dual;   
select (to_char(sysdate, 'yyyy') - substr('430426199303014475', 7, 4)) age from dual;

方法二
[html] view plain copy print?
  1. 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_number(to_char(sysdate,'yyyymmdd'))/10000 - to_number(to_char(to_date('1982-8-28','yyyy-mm-dd'),'yyyymmdd'))/10000) from dual

方法三
[html] view plain copy print?
  1. select trunc((to_char(sysdate,'yyyyMMdd')-to_char(to_date(substr('430426199303014475',7,8),'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位身份证获取年龄性别


[html] view plain copy print?
  1. select   
  2.     case  
  3.         when length(idcard) = 18 then  
  4.                ceil((to_char(sysdate, 'yyyyMMdd') -  
  5.                       to_char(to_date(substr(idcard, 7, 8), 'yyyy-MM-dd'),  
  6.                                'yyyyMMdd')) / 10000)  
  7.         when length(idcard) = 15 then  
  8.                  ceil((to_char(sysdate, 'yyyyMMdd') -  
  9.                       to_char(to_date('19' || substr(idcard, 7, 6),  
  10.                                        'yyyy-MM-dd'),  
  11.                                'yyyyMMdd')) / 10000)  
  12.              end as age,  
  13.     case  
  14.        when length(idcard) = 18 then  
  15.                  decode(mod(to_number(substr(idcard, 17, 1)), 2),  
  16.                         0,'2','1')  
  17.        when length(idcard) = 15 then  
  18.                  decode(mod(to_number(idcard), 2), 0, '2', '1')  
  19.               end as sex  
  20.          from usr where guid='230E20A6FFA1B41CE050AE0AC684959F'   
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"，就得使用如下格式:


[html] view plain copy print?
  1. select  
  2.     case  
  3.         when length(idcard) = 18 then  
  4.                ceil((to_char(sysdate, 'yyyyMMdd') -  
  5.                       to_char(to_date(substr(idcard, 7, 8), 'yyyy-MM-dd'),  
  6.                                'yyyyMMdd')) / 10000)  
  7.         when length(idcard) = 15 then  
  8.                  ceil((to_char(sysdate, 'yyyyMMdd') -  
  9.                       to_char(to_date('19' || substr(idcard, 7, 6),  
  10.                                        'yyyy-MM-dd'),  
  11.                                'yyyyMMdd')) / 10000)  
  12.              end as age,  
  13.     case  
  14.        when length(idcard) = 18 then  
  15.                  decode(mod(to_number(substr(idcard, 17, 1)), 2),  
  16.                         0,'2','1')  
  17.        when length(idcard) = 15 then  
  18.                  decode(mod(to_number(idcard), 2), 0, '2', '1')  
  19.               end as sex,  
  20.     case  
  21.              when length(idcard)=18 then  
  22.            to_char(to_date(substr(idcard, 7, 8), 'yyyy-MM-dd'),'yyyy-MM-dd')  
  23.        when  length(idcard)= 15 then  
  24.                  to_char(to_date('19'||substr(idcard, 7,6), 'yyyy-MM-dd'),'yyyy-MM-dd')  
  25.                    end as birthday,id as mobile,name,guid  
  26.   
  27.          from usr where guid='174BD3D5879C3BF0E050007F010077DC'   
    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'


avatar
文章
阅读
粉丝