sql 语句 验证身份证号码

像这样写个视图就行了：

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

温馨提示：答案为网友推荐，仅供参考

当前网址：https://verywind.cn/ee/vfyfrfxxr.html