Oracle 根据身份证号码判断性别,年龄
一、Oracle根据身份证判断性别:
女生身份证:
372901198711043482
男生身份证:
372901197711043492
SQL语句如下:
select decode(mod (to_number(substr('372901198711043482' ,17, 1)),2 ),0, '女','男' ) as sex from dual;
select decode(mod (to_number(substr('372901197711043492' ,17, 1)),2 ),0, '女','男' ) as sex from dual;
select decode(mod (to_number(substr('372901198711043482' ,17, 1)),2 ),0, '2','1' ) as sex from dual;
select decode(mod (to_number(substr('372901197711043492' ,17, 1)),2 ),0, '2','1' ) as sex from dual;二、Oracle根据身份证判断年龄:
方法一
select (to_char(sysdate, 'yyyy') - substr('372901197711043492', 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('372901197711043492',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'
您阅读本篇文章共花了： 