SQL 2005身份证函数包含验证和15位转18位

摘自：http://soft.zdnet.com.cn/software_zone/2008/0324/781260.shtml

只需使用下面的语句,即可列出所有不符的身份证号码。

SELECT[姓名],[身份证号],b.*FROM [EmployeeWorks].[Base].[职员] CROSS APPLY [LzmTWWorks].[Helper].[IDCard](身份证号) bWHERE NOT [身份证号] IS NULL AND Valid = 0

示例:

SELECT * FROM [LzmTWWorks].[Helper].[IDCard] ('110116200808080010')/*Input            IDCard   Sex   Birthday Region RegionName   RegionFullName    Valid---------- ----------- ---- ----- -------- ------ ------------ ------------ -----110116200808080010 110116200808080014 1     20080808 110116 宽城区  长春市宽城区    0*/

函数：

USE [LzmTWWorks]GOset ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER FUNCTION [Helper].[IDCard] (    @Card    varchar(18))RETURNS @TCard TABLE (     Input    varchar(18)    ,IDCard    varchar(18)    ,Sex    bit    ,Birthday varchar(8)    ,Region    varchar(6)    ,RegionName nvarchar(50)    ,RegionFullName nvarchar(100)    ,Valid    bit)ASBEGIN    DECLARE                 @Input        varchar(18)            ,@IDCard    varchar(18)            ,@Sex        bit            ,@Birthday    varchar(8)            ,@Region    varchar(6)            ,@RegionName varchar(50)            ,@RegionFullName varchar(100)            ,@Valid        bit    DECLARE                  @Length    as smallint            ,@TmpCard    as varchar(18)            ,@IsOld        as bit    SET @Valid = 0    SET @IDCard = ''    SET @Input = ''    IF @Card IS NULL GOTO Finish    SET @Input = LTRIM(RTRIM(@Card)) /*去空格*/    SET @Length = LEN(@Input)    IF NOT @Length IN (15, 18) GOTO Finish /*非15、18位*/    IF @Length = 15        BEGIN            IF ISNUMERIC(@Input) = 0 GOTO Finish /*非数字*/            SET @TmpCard = LEFT(@Input, 6) + '19' + RIGHT(@input, 9) /*补充为17位*/            SET @IsOld = 1        END    ELSE        BEGIN            IF ISNUMERIC(LEFT(@Input, 17)) = 0 GOTO Finish /*非数字*/            SET @TmpCard = LEFT(@Input, 17) /*取前17位*/            SET @IsOld = 0        END    SET @Birthday = SUBSTRING(@TmpCard, 7, 8)    IF ISDATE(@birthday) = 0 GOTO Finish /*非日期*/    --前17位数与相应加权因子的积的和    DECLARE              @Sum as smallint            ,@WI as tinyint            ,@Index as tinyint            ,@Num as tinyint    SET @Sum = 0    SET @Index = 1    WHILE @Index < 18        BEGIN            SET @Num = CAST(SUBSTRING(@TmpCard, @Index, 1) AS tinyint)            SELECT @WI =                CASE @Index                    WHEN 1 THEN 7                    WHEN 2 THEN 9                    WHEN 3 THEN 10                    WHEN 4 THEN 5                    WHEN 5 THEN 8                    WHEN 6 THEN 4                    WHEN 7 THEN 2                    WHEN 8 THEN 1                    WHEN 9 THEN 6                    WHEN 10 THEN 3                    WHEN 11 THEN 7                    WHEN 12 THEN 9                    WHEN 13 THEN 10                    WHEN 14 THEN 5                    WHEN 15 THEN 8                    WHEN 16 THEN 4                    WHEN 17 THEN 2                END            SET @Sum = @Sum + @Num * @WI            SET @Index = @Index + 1        END    --模11    DECLARE @Mod as tinyint    SET @Mod = @Sum % 11    --校验码    DECLARE @Parity as varchar(1)    SELECT @Parity =        CASE @Mod            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'        END    --完整的18位身份证号码    SET @TmpCard = @TmpCard + @Parity    IF @IsOld = 1        SET @Valid = 1    ELSE         IF @Parity = RIGHT(@Input, 1) /*校验*/            SET @Valid = 1    --无论正确与否，都给出有效身份证号码    SET @IDCard = @tmpCard    --取其它信息    SET @Sex = SUBSTRING(@tmpCard, 17, 1) % 2    SET @Region = SUBSTRING(@tmpCard, 1, 6)    SELECT          @RegionName = [Name]        ,@RegionFullName = [Full]    FROM [Helper].[RegionCodeFullName](Default, @Region)     /*从最新版本数据中取区域信息*/    IF @RegionName IS NULL    BEGIN   DECLARE @FirstDate varchar(8) /*第一代身份证的区划码，目前很多已经不在使用。所以，需要从最旧版本的数据中取区域信息*/        SELECT @FirstDate = MIN(FirstDate)         FROM [Private].[RegionCode]                SELECT              @RegionName = [Name]            ,@RegionFullName = [Full]        FROM [Helper].[RegionCodeFullName](             @FirstDate            ,@Region)        ENDFinish:    INSERT INTO @TCard     VALUES(          @Input        ,@IDCard        ,@Sex        ,@Birthday        ,@Region        ,@RegionName        ,@RegionFullName        ,@Valid)        RETURN END