SELECT t.identity_number '身份证号',SUBSTR(t.identity_number,1,2) AS "省份",
SUBSTR(t.identity_number,7,8) "出生年月",
CASE (SUBSTR(t.identity_number,17,1)%2)
WHEN 1 THEN '男'
WHEN 0 THEN '女'
END AS '性别'
FROM user t ;
case
when LENGTH(identity_number) = 15 then
case
when substr(identity_number, 15, 1) in (1, 3, 5, 7, 9) then
'男'
else
'女'
end
when LENGTH(identity_number) = 18 then
case
when substr(identity_number, 17, 1) in (1, 3, 5, 7, 9) then
'男'
else
'女'
end
end as sex,
case
when LENGTH(identity_number) = 15 then
round(datediff(current_date(),
concat('19',
substr(identity_number, 7, 2),
'-',
substr(identity_number, 9, 2),
'-',
substr(identity_number, 11, 2))) / 365)
when LENGTH(identity_number) = 18 then
round(datediff(current_date(),
concat(substr(identity_number, 7, 4),
'-',
substr(identity_number, 11, 2),
'-',
substr(identity_number, 13, 2))) / 365)
end as age