-- 남여 성별 구하기
SELECT
SUBSTR(JUMIN_NUM, 7, 1),
CASE
WHEN SUBSTR(JUMIN_NUM, 7, 1) IN ( '1', '3', '5', '7', '9' ) THEN '남자'
WHEN SUBSTR(JUMIN_NUM, 7, 1) IN ( '2', '4', '6', '8', '0' ) THEN '여자'
ELSE '몰라'
END 성별
FROM MEMBER_TABLE
/
-- 남여 성별 구하기
SELECT
SUBSTR(JUMIN_NUM, 7, 1),
DECODE(SUBSTR(JUMIN_NUM, 7, 1), '1', '남자', '2', '여자', '3', '남자', '4', '여자', '5', '남자', '6', '여자', '7', '남자', '8', '여자', '9', '남자', '0', '여자','몰라')
FROM MEMBER_TABLE
/
-- 나이 계산하기
SELECT
CASE
WHEN SUBSTR(JUMIN_NUM, 7, 1) IN ( '1', '2', '5', '6' ) THEN TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - TO_NUMBER('19'||SUBSTR(JUMIN_NUM, 1,2)))
WHEN SUBSTR(JUMIN_NUM, 7, 1) IN ( '3', '4', '7', '8' ) THEN TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - TO_NUMBER('20'||SUBSTR(JUMIN_NUM, 1,2)))
WHEN SUBSTR(JUMIN_NUM, 7, 1) IN ( '9', '0' ) THEN TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - TO_NUMBER('18'||SUBSTR(JUMIN_NUM, 1,2)))
ELSE '몰라'
END 나이
FROM MEMBER_TABLE
(나온 결과값에 +1 을 해줄지 여부는 확인 필요)
(단순 년도를 이용한 계산이면 생년월일을 따지게 되면 복잡...)
