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

数据库中，身份z18位一般用字符类型，varchar或者char来表示。优选char类型。

在数据库中，char代表固定长度的字符类型。

如：char(18)，就代表可存储18个字符，如果输入时只输入少于18位的字符，后边会以空格补位，身份z正好18位的话，完全可以用这个类型。

在数据库中，varchar代表可变长度的字符类型。

如：varchar(18)，就代表可存储18个字符，如果输入时只输入少于18位的字符，那么这个长度就是字符本身的长度，身份z正好18位的话，也同样可以用这个类型。

可以，数据库里用于记载身份z号码的字段应该使用“字符型”数据类型。尽管绝大多数的身份z号码信息都是由数字组成的，但是不能排除身份z号码身份z号码里需要使用除数字以外的其他字符的可能，例如身份z号码需要含字母、标点符号或以0开始等特殊需求情况，如果字段被设置为数字，那么碰到特殊的身份z号码时就无法存储了，另外身份z号码有18位，如果按数字类型来处理其值也偏大，不利于日常维护和使用。

---