【SQLServer】验证身份证号第18位校验码是否合法

1、业务场景

参考国家标准GB 11643-1999，居民身份证号组成：6位数字地址码+8位数字出生日期码+3位数字顺序码+1位数字校验码，全文已给出校验码的生成算法，通过SQLServer研究校验码生成的可行性，以便于验证身份证号第18位校验码是否合法。

---

2、知识前提

国家标准GB 11643-1999：公民身份号码

---

3、实现方案

SQLServer编写函数，获取正确的身份证号第18位校验码/验证身份证号第18位校验码是否合法。
函数VerifyIdentityNoCRC入参说明：
@identityNo 表示待校验18位身份证号；
@type表示返回类型，0-返回：第18位校验码校验结果；1-返回：算法生成第18位校验码

具体sql语句：

--sqlserver获取正确的身份证号第18位校验码/验证身份证号第18位校验码是否合法VerifyIdentityNoCRC
if exists(select 1 from sysobjects where id=OBJECT_ID('VerifyIdentityNoCRC'))
	drop function VerifyIdentityNoCRC
go
create function VerifyIdentityNoCRC(@identityNo nvarchar(18),@type int)
returns nvarchar(1)
as
begin
	--身份证号：6位数字地址码+8位数字出生日期码+3位数字顺序码+1位数字校验码
	--参考：GB 11643-1999
	declare @identityNoCRC nvarchar(1)--算法生成第18位校验码
	declare @identityNoCRCSrc nvarchar(1)=SUBSTRING(@identityNo,18,1)--待校验第18位校验码
	declare @ai int
	declare @index int=18
	--数据源
	declare @tableCal table(
		i int--字符位置，从右到左
		,ai int--号码字符值
		,Wi int--加权因子值
		,[ai*Wi] int--号码字符值*加权因子值
	)
	while(@index>=2)
	begin
		set @ai=SUBSTRING(@identityNo,19-@index,1)--计算号码字符值	
		insert into @tableCal(i,ai,Wi)
		values(@index,@ai,power(2,@index-1)%11)--计算加权因子值
		set @index=@index-1
	end
	update @tableCal set [ai*Wi]=ai*Wi
	--计算
	declare @sum int=(select sum([ai*Wi]) from @tableCal)
	declare @Emod int=@sum%11--计算公式：sum(号码字符值*加权因子值)%11
	declare @a1 int=(12-@Emod)%11--计算校验码字符值
	set @identityNoCRC=case when @a1=10 then 'X' else convert(nvarchar(2),@a1) end
	--返回
	declare @result nvarchar(1)
	if(@type=0)--返回：第18位校验码校验结果，1：正确，0-错误
		set @result=case when @identityNoCRC=@identityNoCRCSrc then 1 else 0 end
	else if(@type=1)--返回：算法生成第18位校验码
		set @result=@identityNoCRC

	return @result

	--select dbo.VerifyIdentityNoCRC('440524188001010014',0)--验证身份证号第18位校验码是否合法，1-合法
	--select dbo.VerifyIdentityNoCRC('110105194912310027',0)--验证身份证号第18位校验码是否合法，0-不合法
	--select dbo.VerifyIdentityNoCRC('440524188001010014',1)--获取正确的身份证号第18位校验码，4-算法生成
	--select dbo.VerifyIdentityNoCRC('110105194912310027',1)--获取正确的身份证号第18位校验码，X-算法生成
end
go

---

4、测试

测试用sql语句：

--测试
select dbo.VerifyIdentityNoCRC('440524188001010014',0)--验证身份证号第18位校验码是否合法，1-合法
select dbo.VerifyIdentityNoCRC('110105194912310027',0)--验证身份证号第18位校验码是否合法，0-不合法
select dbo.VerifyIdentityNoCRC('440524188001010014',1)--获取正确的身份证号第18位校验码，4-算法生成
select dbo.VerifyIdentityNoCRC('110105194912310027',1)--获取正确的身份证号第18位校验码，X-算法生成

测试结果：
1-验证身份证号第18位校验码是否合法，1-合法

2-验证身份证号第18位校验码是否合法，0-不合法

3-获取正确的身份证号第18位校验码，4-算法生成

4-获取正确的身份证号第18位校验码，X-算法生成

5、参考链接

1、公民身份号码，标准号：GB 11643-1999
2、中华人民共和国行政区划代码，标准号：GB/T 2260-2007
3、数据元和交换格式 信息交换 日期和时间表示法，标准号：GB/T 7408-2005
4、数据处理 校验码系统，标准号：GB/T 17710-1999
5、国家标准全文公开系统