mysql 身份证号码判断
1.位数判断LENGTH(d.cjdxzjhm) =15 or LENGTH(d.cjdxzjhm) = 182.校验含有中文字符length表示字节个数,char_length表示字符,一个中文对应3个字节length(d.cjdxzjhm)<>char_length(d.cjdxzjhm)3.格式校验,匹配15和18位d.cjdxzjhm regexp '^[1-9][[:digi
·
1.位数判断
LENGTH(d.cjdxzjhm) =15 or LENGTH(d.cjdxzjhm) = 18
2.校验含有中文字符
length表示字节个数,char_length表示字符,一个中文对应3个字节
length(d.cjdxzjhm)<>char_length(d.cjdxzjhm)
3.格式校验,匹配15和18位
d.cjdxzjhm regexp '^[1-9][[:digit:]]{7}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}$|^[1-9][[:digit:]]{5}[1-9][[:digit:]]{3}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}([0-9]|X)$'
4.判断年份
substr(d.cjdxzjhm,7,4) not between '1900' and '2014'
5.判断月份
substr(d.cjdxzjhm,11,2) not between '01' and '12'
6.判断日
substr(d.cjdxzjhm,13,2) not between 1 and DATEDIFF(date_add(concat(substr(d.cjdxzjhm,7,4),substr(d.cjdxzjhm,11,2),'01'),interval 1 month ),concat(substr(d.cjdxzjhm,7,4),substr(d.cjdxzjhm,11,2),'01'))
7.不能包含特殊字符,0表示不包含,大于0表示包含
instr(d.cjdxzjhm,'*')=0
instr(d.cjdxzjhm,'*')>0
更多推荐
已为社区贡献1条内容
所有评论(0)