mssql2000 身份证号码验证

CREATE VIEW thisDate
--返回当前日期，因为自定义函数中不能使用GETDATE()
AS 
SELECT convert(VARCHAR(8),getdate(),112) AS aDate

Create function Check_Sfz(@sfzh char(18))
/*mssql2000
返回值=0，身份证校验正确 
       1：位数不对 
       2：含有不规则字符  
       3：日期不对 
       4：校验位不对
*/
returns tinyint
as
begin
 set @sfzh=ltrim(rtrim(@sfzh))
 declare @r char(1),@resu TINYINT,@thisDate VARCHAR(8)
 declare @i INT
 SELECT @thisDate=aDate FROM thisDate
 if len(@sfzh) <> 18 
  set @resu=1
 ELSE
 BEGIN
  IF isnumeric(substring(@sfzh,1,17))=0
      --检查前17位是否为数字
     set @resu=2
  ELSE IF isDate(substring(@sfzh,7,8))=0  
     --日期是否符合规则 
     set @resu=3
  ELSE IF (substring(@sfzh,7,4)<'1900') OR (substring(@sfzh,7,8)>=@thisDate)
     --日期是否符合逻辑
     SET @resu=3
  else
  BEGIN 
      --检查第18位数据的正确性
  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 = (case @i 
   when 0 then '1' when 1 then '0' when 2 then 'x' 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)
   IF @r=lower(substring(@sfzh,18,1)) set @resu=0
   ELSE SET @resu=4--验证位不正确
  end
 end
 return(@resu) 
END

 

转载于:https://www.cnblogs.com/ByBull/p/4664543.html