关于公民身份证号码15位与18位互相转化的问题

来源：互联网发布：软件和信息服务业协会编辑：程序博客网时间：2024/05/21 17:18

--SQL SERVER 15位身份证号转18位身份证

CREATE FUNCTION ID15TO18 (@id15 char(15))
RETURNS CHAR(18) AS
BEGIN
    DECLARE @ID18 CHAR(18),@S18 bigint

    SET @S18 = ( (SUBSTRING(@ID15,1,1)*7) + (SUBSTRING(@ID15,2,1)*9) + (SUBSTRING(@ID15,3,1)*10)
       + (SUBSTRING(@ID15,4,1)*5) + (SUBSTRING(@ID15,5,1)*8) + (SUBSTRING(@ID15,6,1)*4) + (1*2) + (9*1)
       + (SUBSTRING(@ID15,7,1)*6) + (SUBSTRING(@ID15,8,1)*3) + (SUBSTRING(@ID15,9,1)*7)
       + (SUBSTRING(@ID15,10,1)*9)+ (SUBSTRING(@ID15,11,1)*10)+ (SUBSTRING(@ID15,12,1)*5)
       + (SUBSTRING(@ID15,13,1)*8) +(SUBSTRING(@ID15,14,1)*4) + (SUBSTRING(@ID15,15,1)*2) ) % 11

    SET @ID18 = SUBSTRING(@ID15,1,6) + '19' + SUBSTRING(@ID15,7,9) +
        CASE
            WHEN @S18 = 0 THEN '1'
            WHEN @S18 = 1 THEN '0'
            WHEN @S18 = 2 THEN 'X'
            WHEN @S18 = 3 THEN '9'
            WHEN @S18 = 4 THEN '8'
            WHEN @S18 = 5 THEN '7'
            WHEN @S18 = 6 THEN '6'
            WHEN @S18 = 7 THEN '5'
            WHEN @S18 = 8 THEN '4'
            WHEN @S18 = 9 THEN '3'
            WHEN @S18 = 10 THEN '2'
        END

    RETURN @ID18
END

GO

SELECT DBO.ID15TO18('????????????????')

DROP FUNCTION ID15TO18

-- 18位身份证号转15位身份证

CREATE FUNCTION ID18TO15 (@id18 char(18))
RETURNS CHAR(15) AS
BEGIN
    DECLARE @ID15 CHAR(15)

    SET @ID15 = SUBSTRING(@id18,1,6) + SUBSTRING(@id18,9,9)

    RETURN @ID15
END

GO

SELECT DBO.ID18TO15('3210*****605026619')

DROP FUNCTION ID18TO15