mssql2000 身份证号码验证

mssql2000 身份证号码验证

CREATE VIEW thisDate
--返回当前日期，因为自定义函数中不能使用GETDATE()
AS 
SELECT convert(VARCHAR(8),getdate(),112) AS aDateCreate function Check_Sfz(@sfzh char(18))
/*mssql2000
返回值=0，身份证校验正确 1：位数不对 2：含有不规则字符  3：日期不对 4：校验位不对
*/
returns tinyint
as
beginset @sfzh=ltrim(rtrim(@sfzh))declare @r char(1),@resu TINYINT,@thisDate VARCHAR(8)declare @i INTSELECT @thisDate=aDate FROM thisDateif len(@sfzh) <> 18 set @resu=1ELSEBEGINIF isnumeric(substring(@sfzh,1,17))=0--检查前17位是否为数字set @resu=2ELSE IF isDate(substring(@sfzh,7,8))=0  --日期是否符合规则 set @resu=3ELSE IF (substring(@sfzh,7,4)<'1900') OR (substring(@sfzh,7,8)>=@thisDate)--日期是否符合逻辑SET @resu=3elseBEGIN --检查第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) * 2set @i = @i - @i/11 * 11set @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=0ELSE SET @resu=4--验证位不正确endendreturn(@resu) 
END

转载于:.html