MYSQL将15位身份证转为18位身份证

最新推荐文章于 2024-09-05 12:38:05 发布

枫叶梨花

最新推荐文章于 2024-09-05 12:38:05 发布

阅读量440

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/flash_love/article/details/134676108

版权

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