1、号码的结构
公民身份号码是特征组合码,由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为:六位数字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码。2、地址码(前六位数)
表示编码对象常住户口所在县(市、旗、区)的行政区划代码,按GB/T2260的规定执行。3、出生日期码(第七位至十四位)
表示编码对象出生的年、月、日,按GB/T7408的规定执行,年、月、日代码之间不用分隔符。4、顺序码(第十五位至十七位)
表示在同一地址码所标识的区域范围内,对同年、同月、同日出生的人编定的顺序号,顺序码的奇数分配给男性,偶数分配给女性。5、校验码(第十八位数)
(1)十七位数字本体码加权求和公式
S = Sum(Ai * Wi), i = 0, ... , 16 ,先对前17位数字的权求和
Ai:表示第i位置上的身份证号码数字值
Wi:表示第i位置上的加权因子
Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2
(2)计算模
Y = mod(S, 11)
(3)通过模得到对应的校验码
Y: 0 1 2 3 4 5 6 7 8 9 10
校验码: 1 0 X 9 8 7 6 5 4 3 2所以我们就可以大致写一个函数来校验是否正确了。
----------------------------------------------------
不仅需要18位的最后一位的验证,而且需要出生年月,性别,出生地代码是否正确的验证,能区分18位和15位,这里需要一点资料的是,谁有地区代码的资料,需要加载进来
公民身份号码是特征组合码,由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为:六位数字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码。2、地址码(前六位数)
表示编码对象常住户口所在县(市、旗、区)的行政区划代码,按GB/T2260的规定执行。3、出生日期码(第七位至十四位)
表示编码对象出生的年、月、日,按GB/T7408的规定执行,年、月、日代码之间不用分隔符。4、顺序码(第十五位至十七位)
表示在同一地址码所标识的区域范围内,对同年、同月、同日出生的人编定的顺序号,顺序码的奇数分配给男性,偶数分配给女性。5、校验码(第十八位数)
(1)十七位数字本体码加权求和公式
S = Sum(Ai * Wi), i = 0, ... , 16 ,先对前17位数字的权求和
Ai:表示第i位置上的身份证号码数字值
Wi:表示第i位置上的加权因子
Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2
(2)计算模
Y = mod(S, 11)
(3)通过模得到对应的校验码
Y: 0 1 2 3 4 5 6 7 8 9 10
校验码: 1 0 X 9 8 7 6 5 4 3 2所以我们就可以大致写一个函数来校验是否正确了。
----------------------------------------------------
不仅需要18位的最后一位的验证,而且需要出生年月,性别,出生地代码是否正确的验证,能区分18位和15位,这里需要一点资料的是,谁有地区代码的资料,需要加载进来
Function IDCheck(e)
IDCheck = true
arrVerifyCode = Split("1,0,x,9,8,7,6,5,4,3,2", ",")
Wi = Split("7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2", ",")
Checker = Split("1,9,8,7,6,5,4,3,2,1,1", ",")
If Len(e) < 15 Or Len(e) = 16 Or Len(e) = 17 Or Len(e) > 18 Then
'IDCheck= "身份证号共有 15 码或18位"
IDCheck = False
Exit Function
End If
Dim Ai
If Len(e) = 18 Then
Ai = Mid(e, 1, 17)
ElseIf Len(e) = 15 Then
Ai = e
Ai = Left(Ai, 6) & "19" & Mid(Ai, 7, 9)
End If
If Not IsNumeric(Ai) Then
'IDCheck= "身份证除最后一位外,必须为数字!"
IDCheck = False
Exit Function
End If
Dim strYear, strMonth, strDay
strYear = CInt(Mid(Ai, 7, 4))
strMonth = CInt(Mid(Ai, 11, 2))
strDay = CInt(Mid(Ai, 13, 2))
BirthDay = Trim(strYear) + "-" + Trim(strMonth) + "-" + Trim(strDay)
If IsDate(BirthDay) Then
If DateDiff("yyyy",Now,BirthDay)<-140 or cdate(BirthDay)>date() Then
'IDCheck= "身份证输入错误!"
IDCheck = False
Exit Function
End If
If strMonth > 12 Or strDay > 31 Then
IDCheck = False
'IDCheck= "身份证输入错误!"
Exit Function
End If
Else
'IDCheck= "身份证输入错误!"
IDCheck = False
Exit Function
End If
Dim i, TotalmulAiWi
For i = 0 To 16
TotalmulAiWi = TotalmulAiWi + CInt(Mid(Ai, i + 1, 1)) * Wi(i)
Next
Dim modValue
modValue = TotalmulAiWi Mod 11
Dim strVerifyCode
strVerifyCode = arrVerifyCode(modValue)
Ai = Ai & strVerifyCode
IDCheck = Ai
If Len(e) = 18 And e <> Ai Then
'IDCheck= "身份证号码输入错误!"
IDCheck = False
Exit Function
End If
End Function
Dim IDCode15to18 As String '输出的18位二代身份证号
Dim i As Short
Dim num As Short
Dim code As String
num = 0
IDCode15to18 = Left(sCode15, 6) & "19" & Right(sCode15, 9)
' 计算校验位
For i = 18 To 2 Step -1
num = num + (2 ^ (i - 1) Mod 11) * CDbl(Mid(IDCode15to18, 19 - i, 1))
Next i
num = num Mod 11
Select Case num
Case 0
code = "1"
Case 1
code = "0"
Case 2
code = "X"
Case Else
code = Trim(Str(12 - num))
End Select
IDCode15to18 = IDCode15to18 & code
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER Function dbo.F_IdCard15to18(@sCode15 varchar(15))
returns varchar(18)
as
begin
declare @IDCode15to18 as varchar(18) --输出的18位二代身份证号
declare @i as int
declare @num as int
--declare @code as varchar(10)
set @num=0
set @IDCode15to18=Left(@sCode15,6)+'19'+Right(@sCode15, 9)--计算校验位
set @i=18
while @i>=1
begin
select @num=@num+(power(2,(@i-1))%11)*cast(substring(@IDCode15to18,19-@i,1) as int)
select @i=@i-1
end
select @num=@num%11
select @IDCode15to18=@IDCode15to18+case @num
when 0 then '1'
when 1 then '0'
when 2 then 'X'
else rtrim(12-@num) end
return @IDCode15to18
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
参数说明
@vidcard:需要处理的身份证号
@ixb:身份证中需要对性别进行验证的1男0女-1表示不进行性别验证
@dtoday:今天日期,用于计算年龄和验证身份证的在效性返回表字段说明
idcard:原身份证号
xym:校验码1成功,0失败
xx:校验信息
newidcard:新身份证号,用于15位转18位
csny:出生年月
nl_y:虚岁年龄
nl_m:实际年龄,按月
nl_d:实际年龄,按天
xb:性别
csd:出生地
xz:星座
sx:生宵调用方式
declare @d datetime
set @d=getdate()
select * from dbo.fun_id_card('身份证号',-1,@d)
*/
create function fun_id_card(@vidcard varchar(18),@ixb int,@dtoday datetime)
returns @retidcardreports table(idcard varchar(18) primary key,
xym int not null,
xx varchar(100) not null,
newidcard varchar(18) not null,
csny varchar(8) not null,
nl_y int not null,
nl_m float not null,
nl_d float not null,
xb int not null,
xb_c varchar(4) not null,
csd varchar(100) not null,
xz varchar(20) not null,
sx varchar(4) not null
)
as
begin
declare @new_id_card varchar(18)
declare @birthday varchar(8)
declare @nl_y int
declare @nl_m float
declare @nl_d float
declare @xym int
declare @xx varchar(100)
declare @isdate int
declare @num varchar(17)
declare @i int
declare @sum int
declare @verifycode varchar(1)
declare @m int
declare @xb int
declare @xb_c varchar(4)
declare @csd varchar(100)
declare @xz varchar(10)
declare @sx varchar(2) set @xym=1
set @xx='身份证合法' If Len(@vidcard) < 15 Or Len(@vidcard) = 16 Or Len(@vidcard) = 17 Or Len(@vidcard) > 18
begin
set @xym=0
set @xx='身份证只允许有15位号码或18位号码'
end If Len(@vidcard) = 18 set @num = left(@vidcard, 17)
else If Len(@vidcard) = 15 set @num = Left(@vidcard, 6) + '19' + Right(@vidcard, 9)
else set @num=''
If (IsNumeric(@num)=0) and (@xym=1)
begin
set @xym=0
set @xx='身份证除最后一位外,必须为数字'
End if @xym=1 select @birthday=case len(@vidcard) when 18 then substring(@vidcard,7,8) when 15 then '19'+substring(@vidcard,7,6) end
else select @birthday= ''
select @isdate=isdate(@birthday)
if @xym=1
begin
if @isdate=0
begin
set @xym=0
set @xx='身份证出生日期无效'
set @birthday= ''
end
else if (DateDiff(yyyy,@dtoday,cast(@birthday as datetime))<-140) or
(DateDiff(dd,@dtoday,cast(@birthday as datetime))>1)
begin
set @xym=0
set @xx='身份证出生日期范围无效'
end
end
if @xym=1
begin
select @nl_y=datediff(yyyy, @birthday, @dtoday)+1 select @nl_m=case len(@vidcard) when 18 then cast(datediff(mm,substring(@vidcard,7,8),@dtoday) as real)/12
else cast(datediff(mm,substring(@vidcard,7,6),@dtoday) as real)/12 end select @nl_d=datediff(yyyy, @birthday, @dtoday) +
1.0 * datediff(dd, dateadd(yyyy, datediff(yyyy, @birthday, @dtoday), @birthday),@dtoday) /
case when (year(@dtoday) % 4)+(year(@dtoday) % 400) = 0 then 366 else 365 end
end
else select @nl_y=0,@nl_m=0,@nl_d=0 if @xym=1
begin
set @m=cast(right(@num,3) as int) % 2
select @xb=case @m when 0 then 0 else 1 end,@xb_c=case @m when 0 then '女' else '男' end
if (@ixb<>-1) and (@ixb<>@xb)
begin
set @xym=0
set @xx='性别有误'
end
end
else select @xb=-1,@xb_c='未知' set @new_id_card=''
if @xym=1
begin
set @sum=0
set @i=18
while @i>=1
begin
select @sum=@sum+(power(2,(@i-1))%11)*cast(substring(@num,19-@i,1) as int)
select @i=@i-1
end
select @sum=@sum%11
select @verifycode=case @sum when 0 then '1' when 1 then '0' when 2 then 'X' else rtrim(12-@sum) end
if (@verifycode<>right(@vidcard,1)) and (len(@vidcard)=18)
begin
set @xym=0
set @xx='身份证校验码错误'
end
else set @new_id_card=@num+@verifycode
end set @csd=''
if @xym=1
begin
select @csd=isnull(f_dqmc,'') from tbda_dq where f_dqbm=left(@vidcard,2)
if right(@vidcard,4)<>'0000' select @csd=@csd+ isnull(f_dqmc,'') from tbda_dq where f_dqbm=left(@vidcard,4)
if right(@vidcard,2)<>'00' select @csd=@csd+ isnull(f_dqmc,'') from tbda_dq where f_dqbm=left(@vidcard,6)
end set @xz=''
set @sx='' INSERT into @retidcardreports(idcard,xym,xx,newidcard,csny,nl_y,nl_m,nl_d,xb,xb_c,csd,xz,sx) select
@vidcard,@xym,@xx,@new_id_card,@birthday,@nl_y,@nl_m,@nl_d,@xb,@xb_c,@csd,@xz,@sx RETURN
endcreate Function fun_IdCard15to18(@vIdCode15 varchar(15))
returns varchar(18)
as
begin
declare @IDCode15to18 as varchar(18)
declare @i as int
declare @num as int
set @num=0
set @IDCode15to18=Left(@vIdCode15,6)+'19'+Right(@vIdCode15, 9)
set @i=18
while @i>=1
begin
select @num=@num+(power(2,(@i-1))%11)*cast(substring(@IDCode15to18,19-@i,1) as int)
select @i=@i-1
end
select @num=@num%11
select @IDCode15to18=@IDCode15to18+case @num when 0 then '1' when 1 then '0' when 2 then 'X' else rtrim(12-@num) end
return @IDCode15to18
end地区表可以参照网上的