SELECT ceil(months_between(sysdate,birthday)/12) AS age, DECODE(mod(sex,2),0,'女',1,'男','其它') AS sex FROM (SELECT to_date(DECODE(LENGTH(a),15,'19' || SUBSTR(a,7,6),18,SUBSTR(a,7,8)),'yyyy-mm-dd') AS birthday, SUBSTR(a,LENGTH(a)) AS sex FROM ( SELECT '111222199010103339' AS a FROM dual UNION ALL SELECT '111222198010103334' FROM dual UNION ALL SELECT '111222651010333' FROM dual ) )AGE SEX ---------------------- --- 22 男 32 女 47 男
SELECT ceil(months_between(sysdate,birthday)/12) AS age, DECODE(mod(sex,2),0,'女',1,'男','其它') AS sex FROM (SELECT to_date(DECODE(LENGTH(a),15,'19' || SUBSTR(a,7,6),18,SUBSTR(a,7,8)),'yyyy-mm-dd') AS birthday, regexp_replace(SUBSTR(a,LENGTH(a)),'[^0-9]',0) AS sex--修改一下,如果出现数字以外的字符,均认为女性。 FROM ( SELECT '111222199010103339' AS a FROM dual UNION ALL SELECT '11122219801010333*' FROM dual UNION ALL SELECT '111222651010333' FROM dual ) )
DECODE(mod(sex,2),0,'女',1,'男','其它') AS sex
FROM
(SELECT to_date(DECODE(LENGTH(a),15,'19' || SUBSTR(a,7,6),18,SUBSTR(a,7,8)),'yyyy-mm-dd') AS birthday,
SUBSTR(a,LENGTH(a)) AS sex
FROM
( SELECT '111222199010103339' AS a FROM dual
UNION ALL
SELECT '111222198010103334' FROM dual
UNION ALL
SELECT '111222651010333' FROM dual
)
)AGE SEX
---------------------- ---
22 男
32 女
47 男
DECODE(mod(sex,2),0,'女',1,'男','其它') AS sex
FROM
(SELECT to_date(DECODE(LENGTH(a),15,'19'
|| SUBSTR(a,7,6),18,SUBSTR(a,7,8)),'yyyy-mm-dd') AS birthday,
regexp_replace(SUBSTR(a,LENGTH(a)),'[^0-9]',0) AS sex--修改一下,如果出现数字以外的字符,均认为女性。
FROM
( SELECT '111222199010103339' AS a FROM dual
UNION ALL
SELECT '11122219801010333*' FROM dual
UNION ALL
SELECT '111222651010333' FROM dual
)
)