22,206
社区成员
发帖
与我相关
我的任务
分享
declare @t table(col varchar(18))
insert @t select '320113197611263611'
insert @t select '320113761126361'
insert @t select '320113761126368'
select col
,case len(col) when 15 then
case cast(right(col,1) as int)%2 when 1 then '男' else '女' end
when 18 then
case cast(substring(col,17,1) as int)%2 when 1 then '男' else '女' end
else '错误' end as 'sexy'
from @t
/*col sexy
------------------ ----
320113197611263611 男
320113761126361 男
320113761126368 女
(3 行受影响)
*/
declare @t table(col varchar(18))
insert @t select '320113197611263611'
insert @t select '320113761126361'
insert @t select '320113761126368'
select col
,case len(col) when 15 then
case cast(right(col,1) as int)%2 when 1 then '男' else '女' end
when 18 then
case cast(substring(col,17,1) as int)%2 when 1 then '男' else '女' end
else '错误' end as 'sexy'
from @t
主要验证SQL数据库中已输入的15位 及18位 身份证号码的位数、出生年月日是否正确,
可以过滤出大部分的输入错误。
or (len(身份证号)=18 and (Substring(身份证号,7,2)<'19' or Substring(身份证号,7,2)>'20'
or (Substring(身份证号,11,2)>12)
or (Substring(身份证号,11,2) in (01,03,05,07,08,10,12) and Substring(身份证号,13,2)>31)
or (Substring(身份证号,11,2) in (04,06,09,11) and Substring(身份证号,13,2)>30)
or (Substring(身份证号,11,2)=02 and Substring(身份证号,13,2)>29)))
---------------------- 下面是针对 15位 及18位 身份证号码性别的验证语句 ------------------
-- Access 不支持 Substring 查询,可以替换为 mid 查询。
select 序号,姓名,身份证号,性别
from 身份表
where (((len(身份证号)=15) and (Substring(身份证号,15,1) in (1,3,5,7,9)) and 性别<>'男')
or ((len(身份证号)=15) and (Substring(身份证号,15,1) in (2,4,6,8,0)) and 性别<>'女'))
or (((len(身份证号)=18) and (Substring(身份证号,17,1) in (1,3,5,7,9)) and 性别<>'男')
or ((len(身份证号)=18) and (Substring(身份证号,17,1) in (2,4,6,8,0)) and 性别<>'女'))
---------------------- 下面是针对 15位 及18位 身份证号码位数与出生年月日的验证 ------------------
-- Access 不支持 Substring 查询,可以替换为 mid 查询。
select 序号,姓名,身份证号,性别
from 身份表
where (len(身份证号)<>15 and len(身份证号)<>18)
or (len(身份证号)=15 and ((Substring(身份证号,9,2)>12)
or (Substring(身份证号,11,2) > 31)
or (Substring(身份证号,9,2) in (01,03,05,07,08,10,12) and Substring(身份证号,11,2)>31)
or (Substring(身份证号,9,2) in (04,06,09,11) and Substring(身份证号,11,2)>30)
or (Substring(身份证号,9,2)=02 and Substring(身份证号,11,2)>29)))
select * from 表名 where (((len(Col005)=15) and (Substring(Col005,15,1) in (1,3,5,7,9)) and 性别 <>'男')select case when (len(身份证号码)=18 and cast(substring(身份证号码,17,1) as int)%2=1)
or( len(身份证号码)=15 and cast(right(身份证号码,1) as int)%2=1 then '男'
else '女' end as 性别
from tb
下面是针对 15位 及18位 身份证号码性别的验证语句
select 序号,姓名,身份证号,性别
from 身份表
where (((len(身份证号)=15) and (Substring(身份证号,15,1) in (1,3,5,7,9)) and 性别<>‘男‘)
or ((len(身份证号)=15) and (Substring(身份证号,15,1) in (2,4,6,8,0)) and 性别<>‘女‘))
or (((len(身份证号)=18) and (Substring(身份证号,17,1) in (1,3,5,7,9)) and 性别<>‘男‘)
or ((len(身份证号)=18) and (Substring(身份证号,17,1) in (2,4,6,8,0)) and 性别<>‘女‘))