SQL 校验身份证格式

USE [LoanManagement]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_IDCardChk]    Script Date: 2019/5/17 14:05:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
---------------------------
编制人：Denghejing
功能描述：校验身份证号是否有效 成功返回1 失败返回0
2019-05-16

调用：select [dbo].[fn_IDCardChk]('145124512121')
---------------------------
*/
ALTER 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;
 
 

居民身份证查询验证：http://www.ip33.com/shenfenzheng.html

转载于:https://www.cnblogs.com/Denghejing/p/10881020.html