CREATE FUNCTION sfzh_change (
idcard VARCHAR ( 50 )) RETURNS VARCHAR ( 50 ) CHARSET utf8 BEGIN
DECLARE
new_idcard VARCHAR ( 20 ) DEFAULT '';
DECLARE
id_len VARCHAR ( 20 ) DEFAULT '';
SET id_len = LENGTH(
TRIM( idcard ));
IF
id_len = 15 THEN
SET new_idcard = CONCAT (
SUBSTRING( idcard, 1, 6 ),
( CASE WHEN SUBSTRING( idcard, 7, 2 )> SUBSTRING( YEAR ( CURDATE()), 3, 2 ) THEN '19' ELSE '20' END ),
SUBSTRING( idcard, 7, 9 ),(
CASE
(
(
SUBSTRING( idcard, 1, 1 )* 7+ SUBSTRING( idcard, 2, 1 )* 9+ SUBSTRING( idcard, 3, 1 )* 10+ SUBSTRING( idcard, 4, 1 )* 5+ SUBSTRING( idcard, 5, 1 )* 8+ SUBSTRING( idcard, 6, 1 )* 4+ (
CASE
WHEN SUBSTRING( idcard, 7, 2 )> SUBSTRING( YEAR ( CURDATE()), 3, 2 ) THEN
1 * 2+9 * 1 ELSE 2 * 2+0 * 1
END
)+ SUBSTRING( idcard, 7, 1 )* 6+ SUBSTRING( idcard, 8, 1 )* 3+ SUBSTRING( idcard, 9, 1 )* 7+ SUBSTRING( idcard, 10, 1 )* 9+ SUBSTRING( idcard, 11, 1 )* 10+ SUBSTRING( idcard, 12, 1 )* 5+ SUBSTRING( idcard, 13, 1 )* 8+ SUBSTRING( idcard, 14, 1 )* 4+ SUBSTRING( idcard, 15, 1 )* 2
)% 11
)
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' ELSE 'FALSE'
END
)
);
ELSE
SET new_idcard = idcard;
END IF;
RETURN new_idcard;
END
MYSQL将15位身份证转为18位身份证
最新推荐文章于 2024-09-05 12:38:05 发布