SQLServer State:
1、对身份证日期(年月日)错误进行检查
select cbfbm,cyzjhm,cyxm ,Substring(cyzjhm,7,4)+'-'+Substring(cyzjhm,11,2)+'-'+Substring(cyzjhm,13,2) from cbf_jtcy where (len(cyzjhm)<>15 and len(cyzjhm)<>18) or (Substring(cyzjhm ,7,2)<19) or (Substring(cyzjhm ,7,2)>20) or (Substring(cyzjhm ,11,2)>12) or (Substring(cyzjhm ,11,2) in (01,03,05,07,08,10,12) and Substring(cyzjhm ,13,2)>31) or (Substring(cyzjhm ,11,2) in (04,06,09,11) and Substring(cyzjhm ,13,2)>30) or ((Substring(cyzjhm,7,4) % 4=0 and Substring(cyzjhm,7,4) % 100<>0 or Substring(cyzjhm,7,4) % 400=0) and Substring(cyzjhm ,11,2)=2 and Substring(cyzjhm ,13,2)>29) or ((Substring(cyzjhm,7,4) % 4<>0 and Substring(cyzjhm,7,4) % 100=0 or Substring(cyzjhm,7,4) % 400<>0) and Substring(cyzjhm ,11,2)=2 and Substring(cyzjhm ,13,2)>28)
2、身份证对应性别错误进行检查
select * FROM CBF_JTCY where (((len(CYZJHM)=15) and (Substring(CYZJHM,15,1) in (1,3,5,7,9)) and CYXB<>'1') or ((len(CYZJHM)=15) and (Substring(CYZJHM,15,1) in (2,4,6,8,0)) and CYXB<>'2')) or (((len(CYZJHM)=18) and (Substring(CYZJHM,17,1) in (1,3,5,7,9)) and CYXB<>'1') or ((len(CYZJHM)=18) and (Substring(CYZJHM,17,1) in (2,4,6,8,0)) and CYXB<>'2'))
注:如果在Access数据库使用该SQL语句,只需将Substring函数改成mid函数,其余不变