sql 语句 验证身份z号码

create View eVMutiCard

AS

Select a.Badge,a.Name,a.DepID,a.Compid,a.JobID,a.Status,a.EmpType,a.ReportTo,

b.Identification,

N'身份z长度不合常理' 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'身份z具有无效字符' 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'身份z出生日期不合常理' 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'身份z校验位不正确(第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位身份z检验位的dbo.GetCheckIDCardCode如下：

CREATEfunction 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

1、打开excel表格，在表格中可看到身份z号码常出现的问题为“身份z的位数不足”。

2、以及“身份z上的时间不符合实际”，“身份z重复”，点击“D3单元格”。

3、直接按“ctrl+V”复制函数，出现结果为“false”，该函数较复杂，函数的第一部分：确定身份z的位数是否为15位或18位。

4、函数的第二部分：检验身份z号是否唯一，函数第三部分：确定身份z日期是否合法。主要判断月份是否在1-12之间，日期是否超出当月的天数。

5、利用自动填充功能验证下方身份z的正确与否，可看到有问题的身份z均显示为false，此外还可利用条件格式找出重复值，选中要验证的单元格。

6、依次点击“条件格式”、“新建规则”，点击“仅对唯一值或重复值设置格式”，选择“重复”，点击“格式”，挑一个显眼的颜色，设置完成点击“确定”即可。

7、依次点击“条件格式”、“新建规则”，点击“仅对唯一值或重复值设置格式”，选择“重复”，点击“格式”，挑一个显眼的颜色，设置完成点击“确定”即可。