一、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;
最新评论