sql校验身份证有效性-18位

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91

--调用方法 select  dbo.wsh_sfzjy ('身份证号')   ALTER FUNCTION [dbo].[wsh_sfzjy] (  @sfz varchar(20) ) RETURNS varchar(100)   AS BEGIN DECLARE @PY varchar(100) DECLARE @tou varchar(100) DECLARE @wei varchar(100) DECLARE @jiaoyan varchar(100) set @tou='' set @wei=''   if( LEN(@sfz)=18) BEGIN set @tou=substring(@sfz,1,17) set @wei=substring(@sfz,18,1) END  else if(LEN(@sfz)=17) BEGIN set @tou=@sfz end       if(@tou<>'') BEGIN    with sfz as (SELECT @tou as pspt_id)  select     /*pspt_id为用户身份证号码前17位*/         @jiaoyan=( case when substring(a.pspt_id,11,2) not between '01' and '12' then 'cuowu' when substring(a.pspt_id,13,2) not between '01' and '31' then 'cuowu' when ( 12-              ((                 substring(a.pspt_id,1,1)*7+                 substring(a.pspt_id,2,1)*9+                 substring(a.pspt_id,3,1)*10+                 substring(a.pspt_id,4,1)*5+                 substring(a.pspt_id,5,1)*8+                 substring(a.pspt_id,6,1)*4+                 substring(a.pspt_id,7,1)*2+                 substring(a.pspt_id,8,1)*1+                 substring(a.pspt_id,9,1)*6+                 substring(a.pspt_id,10,1)*3+                 substring(a.pspt_id,11,1)*7+                 substring(a.pspt_id,12,1)*9+                 substring(a.pspt_id,13,1)*10+                 substring(a.pspt_id,14,1)*5+                 substring(a.pspt_id,15,1)*8+                 substring(a.pspt_id,16,1)*4+                 substring(a.pspt_id,17,1)*2             )%11) )%11=10 then 'x'       else CONVERT(VARCHAR(5),( 12-              ((                 substring(a.pspt_id,1,1)*7+                 substring(a.pspt_id,2,1)*9+                 substring(a.pspt_id,3,1)*10+                 substring(a.pspt_id,4,1)*5+                 substring(a.pspt_id,5,1)*8+                 substring(a.pspt_id,6,1)*4+                 substring(a.pspt_id,7,1)*2+                 substring(a.pspt_id,8,1)*1+                 substring(a.pspt_id,9,1)*6+                 substring(a.pspt_id,10,1)*3+                 substring(a.pspt_id,11,1)*7+                 substring(a.pspt_id,12,1)*9+                 substring(a.pspt_id,13,1)*10+                 substring(a.pspt_id,14,1)*5+                 substring(a.pspt_id,15,1)*8+                 substring(a.pspt_id,16,1)*4+                 substring(a.pspt_id,17,1)*2             )%11) )%11) end     )       from sfz a   END if(@tou='') set @PY='输入错误' ELSE if(@jiaoyan='cuowu') set @PY='输入错误' else if (@wei=@jiaoyan) set @PY=@sfz else if ( @wei='')   set @PY=CONVERT(VARCHAR(50),@sfz)+@jiaoyan else  set @PY='输入错误' --set @PY=@sfz17+' '+@PY    RETURN @PY END

　　

 

 

 

第二代身份证号组成规则：

a) 身份证号码（18位）= 地址码（6）+ 出生日期码（8）+ 顺序码（3）+校验码（1）；

b) 地址码：保证位数合法即可，无需校验合法性；

c) 出生日期码：格式为YYYYMMDD，需校验日期有效性；

d) 顺序码：男性为奇数，女性为偶数；

e) 校验码：

S = ∑(i = 1, 17) { A[i] * W[i] }

Y = S % 11

校验码 N = (12 - Y) % 11

所以N取值范围是0-10，10在身份证号码中用大写字母'X'表示

i：表示号码字符从左至右不包括校验码字符在内的位置序号

A[i]：表示第i位置上的身份证号码字符值

W[i]：表示第i位置上的加权系数，其数列为7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2