调用示例:
SELECT dbo.[fn_IDCardChk]('123')
/*
---------------------------
校验身份证号是否有效 成功返回1 失败返回0
Jackerson
---------------------------
*/
CREATE FUNCTION [dbo].[fn_IDCardChk] ( @IDCard VARCHAR(18) )
RETURNS BIT
AS
BEGIN
IF LEN(@IDCard) <> 15
AND LEN(@IDCard) <> 18--身份证号只有15或18位
RETURN(0);
--如果是15位身份证 则只验证日期和是否数字格式
IF LEN(@IDCard) = 15
IF ISDATE('19' + SUBSTRING(@IDCard, 7, 6)) = 0
OR ISNUMERIC(@IDCard) = 0
RETURN(0);
ELSE
RETURN(1);
--18位身份证 验证日期 校验位
IF ISDATE(SUBSTRING(@IDCard, 7, 8)) = 0
OR ISNUMERIC(SUBSTRING(@IDCard, 1, 17)) = 0--验证日期和前17位是否数字格式
RETURN(0);
--验证校验位开始
DECLARE @validFactors VARCHAR(17) ,
@validCodes VARCHAR(11) ,
@i TINYINT ,
@iTemp INT;
SELECT @validFactors = '79A584216379A5842',
@validCodes = '10X98765432', @i = 1, @iTemp = 0;
WHILE @i < 18
BEGIN
SELECT @iTemp = @iTemp
+ CAST(SUBSTRING(@IDCard, @i, 1) AS INT)
* ( CASE SUBSTRING(@validFactors, @i, 1)
WHEN 'A' THEN 10
ELSE SUBSTRING(@validFactors, @i, 1)
END ), @i = @i + 1;
END;
IF SUBSTRING(@validCodes, @iTemp % 11 + 1, 1) = RIGHT(@IDCard, 1)
RETURN 1;
ELSE
RETURN 0;
RETURN NULL;
END;
GO