身份证15转18位存储过程

BEGIN

DECLARE id17 VARCHAR (17);

DECLARE id18 VARCHAR (18);

DECLARE retIc VARCHAR (18) ;

DECLARE v_sum INT ;

DECLARE v_y INT ;

DECLARE v_i INT ;

DECLARE is_num VARCHAR(1);

SET @wf = "7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2" ;

-- 定义数组存放校验码（check code）

SET @cc = "1,0,X,9,8,7,6,5,4,3,2" ;

SET v_sum = 0 ;

SET v_y = 0 ;

SET v_i = 1 ;

select (identityCard REGEXP '[^0-9.]') into @is_num;

-- 判断传入id长度

IF CHAR_LENGTH(identityCard) <>15 THEN

SET retIc = identityCard ;

 

ELSE

IF @is_num = 0 THEN -- 传入15位证件号码均为数字

SET id17 = CONCAT(

CONCAT(SUBSTRING(identityCard, 1, 6), '19'),

SUBSTRING(identityCard, 7)

) ;

-- 十七位数字本体码加权求和

REPEAT

SET v_sum = v_sum + SUBSTRING(id17, v_i, 1) * SUBSTRING_INDEX(

SUBSTRING_INDEX(@wf, ',', v_i),

",",

- 1

) ;

SET v_i = v_i + 1 ;

UNTIL v_i > 17

END REPEAT;

-- 计算模

SET v_y = MOD(v_sum, 11) ;

-- 17位数字 + 通过模得到对应的校验码

SET id18 = CONCAT(

id17,

SUBSTRING_INDEX(

SUBSTRING_INDEX(@cc, ",", v_y + 1),

",",

- 1

)

) ;

SET retIc = id18 ;

END IF;

END IF ;

RETURN retIc;

END