[转sql身份证]

SQL code -- ===================================================================
-- 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

转载于:https://www.cnblogs.com/ruyi/archive/2009/07/11/1521374.html