• 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
  • 相关阅读:
    剑指 Offer 42. 连续子数组的最大和
    剑指 Offer 41. 数据流中的中位数
    剑指 Offer 40. 最小的k个数
    剑指 Offer 39. 数组中出现次数超过一半的数字
    20210510日报
    20210507日报
    20210506日报
    20210505日报
    20210504日报
    20210503日报
  • 原文地址:https://www.cnblogs.com/ByBull/p/4664543.html
Copyright © 2020-2023  润新知