当前位置: 首页 > 图文教程 > 数据库 > MSSQL > SQL 2005身份证函数包含验证和15位转18位

MSSQL
SQL语句技巧:按月统计数据
分页 SQLServer存储过程
SQL语句的基本语法
模糊查询
直接循环写入数据
删除重复记录
删除重复的记录,并保存第一条记录或保存最后一条记录
MSSQL数据库的定期自动备份计划。
一个比较实用的大数据量分页存储过程
数据库存储过程分页显示
ACCESS数据库的压缩,备份,还原,下载,删除的实现
sql编程的几个常识
SQL连接查询介绍
Sql Server基本函数
分页的存储过程
SQL Server全文索引服务
如何在SQLSERVER中快速有条件删除海量数据
整理一下SQLSERVER的排序规则
SQL Server各种日期计算方法(收藏)
全文检索技术 sql server

No. « 2 3 4 5 »

技术文章搜索

关键字

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

出处:互联网   整理: 软晨网（RuanChen.com）   发布: 2009-09-28   浏览: 109 ::
收藏到网摘: n/a

本文主要介绍了SQL Server 2005的身份证函数,其中包含验证和15位转18位。

具体示例代码如下：

以下为引用的内容：

USE [LzmTWWorks]
GO
/****** 对象:  UserDefinedFunction [Helper].[IDCard]    脚本日期: 12/13/2007 19:16:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [Helper].[IDCard]
(
    @Card    varchar(18)
)
RETURNS
@TCard TABLE
(
     Input    varchar(18)
    ,IDCard    varchar(18)
    ,Valid    bit
)
AS
BEGIN
    DECLARE   
             @Input        as varchar(18)
            ,@IDCard    as varchar(18)
            ,@Valid        as 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

    DECLARE @Birthday    varchar(8)
    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

Finish:
    INSERT INTO @TCard VALUES(@Input, @IDCard, @Valid)   
    RETURN
END

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

以下为引用的内容：
SELECT
[姓名]
,[身份证号]
,b.*
FROM [EmployeeWorks].[Base].[职员]
CROSS APPLY [LzmTWWorks].[Helper].[IDCard](身份证号) b
WHERE NOT [身份证号] IS NULL AND Valid = 0

补充信息：

以下为引用的内容：
USE [LzmTWWorks]
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER 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
)
AS
BEGIN
    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)   
    END

Finish:
    INSERT INTO @TCard
    VALUES(
         @Input
        ,@IDCard
        ,@Sex
        ,@Birthday
        ,@Region
        ,@RegionName
        ,@RegionFullName
        ,@Valid)   
    RETURN
END

示例:

以下为引用的内容：

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

*/