sql server中使用函数验证身份证号码是否合法

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name >
-- Create date: <Create Date, , >
-- Description: <Description, , >
-- =============================================
ALTER FUNCTION [dbo].[fn_IsValidIDCard]
(
    @IDCardNo varchar(50)=''
)
RETURNS bit
AS
/*******************************************************************
函数名称：fn_IsvalidIDCard()
参数:@IDCardNo string 身份证号码
返回值：  bit 是否有效
功能描述：判断身份证号码是否合法

备注：目前中国的身份证号码有18位和15位.
1，18位身份证号码的组成:6位地区编码＋8位出生年月日＋3位编号（奇男偶女）＋1位校验码
2，15位身份证号码的组成:6位地区编码＋6位出生年月日＋3位编号（奇男偶女）
*******************************************************************/
BEGIN

declare @Length int, 
@Loop int, 
@Sum int
declare @SingleChar char

set @Sum = 0
if @IDCardNo is null or @IDCardNo = null 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,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