sql 中判断身份证男女等

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  ;