- 西柚不是西游
-
像这样写个视图就行了:
create View eVMutiCard
AS
Select a.Badge,a.Name,a.DepID,a.Compid,a.JobID,a.Status,a.EmpType,a.ReportTo,
b.Identification,
N"身份证长度不合常理" As Remark
From employee b
Where (Len(b.Identification) Not In (15,18)
And b.Identification Is Not Null )
Or b.Identification is Null
Union All
Select a.Badge,a.Name,a.DepID,a.Compid,a.JobID,a.Status,a.EmpType,a.ReportTo,
b.Identification,
N"身份证具有无效字符" As Remark
From employee b
Where Len(b.Identification) In (15,18)
And Isnumeric(Case Len(b.Identification) When 18 Then Substring(b.Identification,1,17)
Else b.Identification End) = 0
Union All
Select a.Badge,a.Name,a.DepID,a.Compid,a.JobID,a.Status,a.EmpType,a.ReportTo,
b.Identification,
N"身份证出生日期不合常理" As Remark
From employee b
Where Len(b.Identification) In (15,18)
And (IsDate(Case When Len(b.Identification)=15 Then "19"+Substring(b.Identification,7,2)+"-"+Substring(b.Identification,9,2)+"-"+Substring(b.Identification,11,2)
Else Substring(b.Identification,7,4)+"-"+Substring(b.Identification,11,2)+"-"+Substring(b.Identification,13,2)
End)=0
Or Not (
(Case When Len(b.Identification)=15 Then "19"+Substring(b.Identification,7,2)+"-"+Substring(b.Identification,9,2)+"-"+Substring(b.Identification,11,2)
Else Substring(b.Identification,7,4)+"-"+Substring(b.Identification,11,2)+"-"+Substring(b.Identification,13,2)
End) Between "1900-01-01" And "2079-06-06"))
Union All
Select a.Badge,a.Name,a.DepID,a.Compid,a.JobID,a.Status,a.EmpType,a.ReportTo,
b.Identification,
N"身份证校验位不正确(第18位与校验不符)" As Remark
From employee b
Where (Len(b.Identification) = 18
And substring(b.Identification,18,19) <> dbo.GetCheckIDCardCode(b.Identification)
And b.Identification Is Not Null)
其中跟据国家规定的计算公式,计算18位身份证检验位的dbo.GetCheckIDCardCode如下:
CREATE function GetCheckIDCardCode(@sfzh char(18))
returns char(1)
as
begin
declare @r varchar(2)
declare @i int
if len(@sfzh) <> 18
set @r = 0
else
set @i = cast(substring(@sfzh,1,1) as int) * 7
+cast(substring(@sfzh,2,1) as int) * 9
+cast(substring(@sfzh,3,1) as int) * 10
+cast(substring(@sfzh,4,1) as int) * 5
+cast(substring(@sfzh,5,1) as int) * 8
+cast(substring(@sfzh,6,1) as int) * 4
+cast(substring(@sfzh,7,1) as int) * 2
+cast(substring(@sfzh,8,1) as int) * 1
+cast(substring(@sfzh,9,1) as int) * 6
+cast(substring(@sfzh,10,1) as int) * 3
+cast(substring(@sfzh,11,1) as int) * 7
+cast(substring(@sfzh,12,1) as int) * 9
+cast(substring(@sfzh,13,1) as int) * 10
+cast(substring(@sfzh,14,1) as int) * 5
+cast(substring(@sfzh,15,1) as int) * 8
+cast(substring(@sfzh,16,1) as int) * 4
+cast(substring(@sfzh,17,1) as int) * 2
set @i = @i - @i/11 * 11
set @r = cast((case @i
when 0 then 1
when 1 then 0
when 2 then 11
when 3 then 9
when 4 then 8
when 5 then 7
when 6 then 6
when 7 then 5
when 8 then 4
when 9 then 3
when 10 then 2
else "" end) as char)
if (@r = 11) set @r="X"
else set @r = @r
set @r = "" + @r +""
return @r
end
- 阿啵呲嘚
-
---sql 2000通过,其他数据库,可能要修改相应的函数
declare @id varchar(18)
set @id="310222198102020239"
----如果有空格 把所有@a换成 rtrim(ltrim(@a))
select @id+"--",case when len(@id)=18 and isnumeric(@id)+isnumeric(left(@id,16))*charindex("x",@id)>0
and isdate(substring(@id,7,8))=1
and upper(right(@id,1))=(select max(c) from (select 0 r,"1" c union all select 1 r,"0" c union all
select 2 r,"X" c union all select 3 r,"9" c union all select 4 r,"8" c union all
select 5 r,"7" c union all select 6 r,"6" c union all select 7 r,"5" c union all
select 8 r,"4" c union all select 9 r,"3" c union all select 10 r,"2" c ) a
where r=(substring(@id,1,1)*(power(2,17) % 11)+substring(@id,2,1)*(power(2,16) % 11)+
substring(@id,3,1)*(power(2,15) % 11)+substring(@id,4,1)*(power(2,14) % 11)+
substring(@id,5,1)*(power(2,13) % 11)+substring(@id,6,1)*(power(2,12) % 11)+
substring(@id,7,1)*(power(2,11) % 11)+substring(@id,8,1)*(power(2,10) % 11)+
substring(@id,9,1)*(power(2,9) % 11)+substring(@id,10,1)*(power(2,8) % 11)+
substring(@id,11,1)*(power(2,7) % 11)+substring(@id,12,1)*(power(2,6) % 11)+
substring(@id,13,1)*(power(2,5) % 11)+substring(@id,14,1)*(power(2,4) % 11)+
substring(@id,15,1)*(power(2,3) % 11)+substring(@id,16,1)*(power(2,2) % 11)+
substring(@id,17,1)*(power(2,1) % 11)) % 11 )
then "18位正确" when len(@id)=15 and isnumeric(@id)*isdate("99"+substring(@id,7,6))=1 then "15位正确" else "错误" end
-------------------- --------
310222198102020239-- 18位正确
---从tb表中找出批量不正确的,简单改下
declare @id varchar(18)
set @id="310222198102020239"
----如果有空格 把所有@a换成 rtrim(ltrim(@a)),@a换成字段名
select * from (
select (case when len(@id)=18 and isnumeric(@id)+isnumeric(left(@id,16))*charindex("x",@id)>0
and isdate(substring(@id,7,8))=1
and upper(right(@id,1))=(select max(c) from (select 0 r,"1" c union all select 1 r,"0" c union all
select 2 r,"X" c union all select 3 r,"9" c union all select 4 r,"8" c union all
select 5 r,"7" c union all select 6 r,"6" c union all select 7 r,"5" c union all
select 8 r,"4" c union all select 9 r,"3" c union all select 10 r,"2" c ) a
where r=(substring(@id,1,1)*(power(2,17) % 11)+substring(@id,2,1)*(power(2,16) % 11)+
substring(@id,3,1)*(power(2,15) % 11)+substring(@id,4,1)*(power(2,14) % 11)+
substring(@id,5,1)*(power(2,13) % 11)+substring(@id,6,1)*(power(2,12) % 11)+
substring(@id,7,1)*(power(2,11) % 11)+substring(@id,8,1)*(power(2,10) % 11)+
substring(@id,9,1)*(power(2,9) % 11)+substring(@id,10,1)*(power(2,8) % 11)+
substring(@id,11,1)*(power(2,7) % 11)+substring(@id,12,1)*(power(2,6) % 11)+
substring(@id,13,1)*(power(2,5) % 11)+substring(@id,14,1)*(power(2,4) % 11)+
substring(@id,15,1)*(power(2,3) % 11)+substring(@id,16,1)*(power(2,2) % 11)+
substring(@id,17,1)*(power(2,1) % 11)) % 11 )
then 1 when len(@id)=15 and isnumeric(@id)*isdate("99"+substring(@id,7,6))=1 then 1 else 0 end) result
from tb) a
where result=0
- gitcloud
-
帮你搜了一下,参考参考。
主要验证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)))
- Chen
-
请你把什么样的是不正确写出来,是长度不够18不正确,还是不是以什么开头的不正确,要知道也有人的身份证是15位的
- 慧慧
-
晕。。你可以先在ASP中验证完再提交到数据库啊。。SQL语句能有这么强大的处理提交数据的功能?!