sql身份证号码验证

USE bzb
GO
/****** Object:  UserDefinedFunction [dbo].[sfz]    Script Date: 2025-03-04 14:43:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 检查函数是否存在，如果存在则删除
IF OBJECT_ID('dbo.sfz', 'FN') IS NOT NULL
    DROP FUNCTION [dbo].[sfz];
GO
-- 创建新的函数
CREATE FUNCTION [dbo].[sfz] ( @IDCardNo VARCHAR(50) )
RETURNS BIT
AS 
    BEGIN
        DECLARE @Length INT ,
            @Loop INT ,
            @Sum INT;
        DECLARE @SingleChar CHAR;

        SET @Sum = 0;
        IF @IDCardNo IS NULL
            OR @IDCardNo = '' 
            OR LTRIM(RTRIM(@IDCardNo)) = '' 
            BEGIN
                RETURN 0;
            END;

        SET @Length = LEN(@IDCardNo);
        -- 判断位数
        IF @Length <> 18
            AND @Length <> 15 
            BEGIN
                RETURN 0;  
            END;
        IF @Length = 18 
            BEGIN
                IF ISNUMERIC(LEFT(@IDCardNo, 17)) = 0 
                    BEGIN  
                        RETURN 0;
                    END;
                IF ISDATE(SUBSTRING(@IDCardNo, 7, 4) + '-'
                          + SUBSTRING(@IDCardNo, 11, 2) + '-'
                          + SUBSTRING(@IDCardNo, 13, 2)) = 0 
                    BEGIN
                        RETURN 0;
                    END;
                SET @Loop = 17;
                WHILE ( @Loop >= 1 ) 
                    BEGIN
                        SET @Sum = @Sum
                            + CONVERT(INT, REPLACE(SUBSTRING(@IDCardNo, @Loop,
                                                             1), '.', ''))
                            * ( POWER(2, ( 18 - @Loop )) % 11 );
                        SET @Loop = @Loop - 1;
                    END;
                SET @Loop = @Sum % 11;
                IF @Loop = 0 
                    BEGIN
                        SET @SingleChar = '1';
                    END
                ELSE 
                    IF @Loop = 1 
                        BEGIN
                            SET @SingleChar = '0';
                        END
                    ELSE 
                        IF @Loop = 2 
                            BEGIN
                                SET @SingleChar = 'X';
                            END
                        ELSE 
                            BEGIN
                                SET @SingleChar = CONVERT(VARCHAR(2), ( 12
                                                              - @Loop ));
                            END;
                IF LOWER(RIGHT(@IDCardNo, 1)) <> LOWER(@SingleChar) 
                    BEGIN
                        RETURN 0;
                    END;
            END
        ELSE 
            IF @Length = 15 
                BEGIN
                    IF ISNUMERIC(@IDCardNo) = 0 
                        BEGIN
                            RETURN 0;
                        END;  
                    IF ISDATE('19' + SUBSTRING(@IDCardNo, 7, 2) + '-'
                              + SUBSTRING(@IDCardNo, 9, 2) + '-'
                              + SUBSTRING(@IDCardNo, 11, 2)) = 0 
                        BEGIN
                            RETURN 0;
                        END;
                END;

        RETURN 1;

    END;
GO

SELECT *  FROM bzb.[dbo].表名  WHERE  bzb.dbo.sfz(LEFT(身份证号,18)) = 0