sql强人使用数据库计算身份证算法

一、

--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

 二、

C# code 
-- 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

 

三、

--===================================================================
--15位号码升级为18位
--===================================================================
Create FUNCTION [dbo].[ID15TO18] (@id15 char(15)) 
/*功能：将身份证的15位号码升级为18位

 
-- 
-- --调用函数
-- update 
--     表
-- set 
--     身份证号 = dbo.ID15TO18(身份证号)
-- where
--     LEN(身份证号) = 15
-- 
-- 
*/
RETURNS CHAR(18)
AS 
BEGIN 
    DECLARE @ID18 CHAR(18)
   
    DECLARE @S1 AS INTEGER
    DECLARE @S2 AS INTEGER
    DECLARE @S3 AS INTEGER
    DECLARE @S4 AS INTEGER
    DECLARE @S5 AS INTEGER
    DECLARE @S6 AS INTEGER
    DECLARE @S7 AS INTEGER
    DECLARE @S8 AS INTEGER
    DECLARE @S9 AS INTEGER
    DECLARE @S10 AS INTEGER
    DECLARE @S11 AS INTEGER
    DECLARE @S12 AS INTEGER
    DECLARE @S13 AS INTEGER
    DECLARE @S14 AS INTEGER
    DECLARE @S15 AS INTEGER
    DECLARE @S16 AS INTEGER
    DECLARE @S17 AS INTEGER
    DECLARE @S18 AS INTEGER
   
    SET @S1=SUBSTRING(@ID15,1,1)
    SET @S2=SUBSTRING(@ID15,2,1)
    SET @S3=SUBSTRING(@ID15,3,1)
    SET @S4=SUBSTRING(@ID15,4,1)
    SET @S5=SUBSTRING(@ID15,5,1)
    SET @S6=SUBSTRING(@ID15,6,1)
    SET @S7=1
    SET @S8=9
    SET @S9=SUBSTRING(@ID15,7,1)
    SET @S10=SUBSTRING(@ID15,8,1)
    SET @S11=SUBSTRING(@ID15,9,1)
    SET @S12=SUBSTRING(@ID15,10,1)
    SET @S13=SUBSTRING(@ID15,11,1)
    SET @S14=SUBSTRING(@ID15,12,1)
    SET @S15=SUBSTRING(@ID15,13,1)
    SET @S16=SUBSTRING(@ID15,14,1)
    SET @S17=SUBSTRING(@ID15,15,1)
   
    SET @S18=((@S1*7)+(@S2*9)+(@S3*10)+(@S4*5)+(@S5*8)+(@S6*4)+(@S7*2)+(@S8*1)
        +(@S9*6)+(@S10*3)+(@S11*7)+(@S12*9)+(@S13*10)+(@S14*5)+(@S15*8)+(@S16
        *4)+(@S17*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