mysql生成首字母简拼

尽力了就好

已于 2023-08-14 09:26:12 修改

阅读量213

点赞数

分类专栏： sql 文章标签： mysql 数据库

于 2023-08-10 17:37:15 首次发布

本文链接：https://blog.csdn.net/A_ZhengCSDN/article/details/132214808

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

应用于mysql数据库，根据用户姓名生成对应首字母简拼，实现方式如下：

实现代码

1.创建存储过程GET_JP()

DROP FUNCTION IF	EXISTS `GET_JP`;
CREATE FUNCTION `GET_JP` ( PARAM VARCHAR ( 255 ) ) RETURNS VARCHAR ( 4 ) CHARSET UTF8 
BEGIN -- 姓名一般不超过四个中文字符，这里定义四个字段对应每一个中文字符
	DECLARE	V_FIRST VARCHAR(256);
	DECLARE	V_FIRST_CHAR VARCHAR(256);
	DECLARE	V_SECOND VARCHAR (256);
	DECLARE	V_SECOND_CHAR VARCHAR(256);
	DECLARE	V_THIRD VARCHAR(256);
	DECLARE	V_THIRD_CHAR VARCHAR(256);
	DECLARE	V_FOUTH VARCHAR(256);
	DECLARE	V_FOUTH_CHAR VARCHAR(256);
	DECLARE	V_CONCAT VARCHAR(256);
		SET V_FIRST_CHAR = UPPER(SUBSTR(PARAM,1,1));
		SET V_FIRST = V_FIRST_CHAR;
		SET V_SECOND_CHAR = UPPER(SUBSTR(PARAM,2,1));
		SET V_SECOND = V_SECOND_CHAR;
		SET V_THIRD_CHAR = UPPER(SUBSTR(PARAM,3,1));
		SET V_THIRD = V_THIRD_CHAR;
		SET V_FOUTH_CHAR = UPPER(SUBSTR(PARAM,4,1));
		SET V_FOUTH = V_FOUTH_CHAR;
		IF	LENGTH(PARAM) <> CHARACTER_LENGTH(PARAM) THEN
			IF	CHARACTER_LENGTH(PARAM) > 0 THEN
			SET V_FIRST = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT (V_FIRST USING gbk ),1)),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
			END IF;
			IF	CHARACTER_LENGTH(PARAM) > 1 THEN
			SET V_SECOND = ELT(INTERVAL(CONV(HEX(LEFT (CONVERT (V_SECOND USING gbk ),1)),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
			END IF;
			IF	CHARACTER_LENGTH(PARAM) > 2 THEN
			SET V_THIRD = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT (V_THIRD USING gbk ),1)),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
			END IF;
			IF	CHARACTER_LENGTH(PARAM) > 3 THEN
			SET V_FOUTH = ELT(INTERVAL(CONV(HEX(LEFT( CONVERT (V_FOUTH USING gbk ),1)),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
			END IF;
		END IF;
		SET V_CONCAT = concat(V_FIRST,V_SECOND,V_THIRD,V_FOUTH);
	RETURN V_CONCAT;
END

2.sql查询

select GET_JP(columns)  from tablename；

例如：

select GET_JP(last_name)  as '简拼',last_name as '姓名' from users;

3.查询结果

代码更新

使用while循环遍历姓名字数

DROP FUNCTION IF	EXISTS `GET_JP`;
CREATE FUNCTION `GET_JP` ( PARAM VARCHAR ( 255 ) ) RETURNS VARCHAR ( 44 ) CHARSET UTF8 
BEGIN -- 姓名一般不超过四个中文字符，这里定义四个字段对应每一个中文字符
	DECLARE	V_FIRST VARCHAR(256);
	DECLARE	V_CHAR_LENGTH	VARCHAR(256);
	DECLARE	V_CONCAT	VARCHAR(256) default '';
	DECLARE i int default 1;
		SET V_CHAR_LENGTH = CHARACTER_LENGTH(PARAM);
		WHILE i <= V_CHAR_LENGTH
			DO
			SET V_FIRST = UPPER(SUBSTR(PARAM,i,1));
			SET V_FIRST = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT (V_FIRST USING gbk ),1)),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
				SET V_CONCAT = concat(V_CONCAT,V_FIRST);
				set i = i + 1 ;
				END WHILE;
	RETURN V_CONCAT;
END;