SELECT t.id_card '身份证号',SUBSTR(t.id_card,1,2) AS "省份",
SUBSTR(t.id_card,7,4) "出生年月",
SUBSTR(t.id_card,17,1) "性别" ,
CASE (SUBSTR(t.id_card,17,1)%2)
WHEN 1 THEN '男'
WHEN 0 THEN '女'
END AS '性别'
FROM bzj_employee t ;
效果图:
更改:
当身份证号码倒数第二位可以被2整除时,性别是女生,不能被整除时,性别是男生
UPDATE bzj_employee t SET t.sex ='女' WHERE SUBSTR(t.id_card,17,1)%2 = 0 ;
UPDATE bzj_employee t SET t.sex ='男' WHERE SUBSTR(t.id_card,17,1)%2 = 1 ;