mysql 中如何取得汉字字段的各汉字首字母,特殊字符用‘#’

最新推荐文章于 2024-07-19 17:03:47 发布

chenwucsdn

最新推荐文章于 2024-07-19 17:03:47 发布

阅读量784

点赞数 1

分类专栏： mysql 文章标签： mysql 首字母

本文链接：https://blog.csdn.net/chenwucsdn/article/details/81325235

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

DROP FUNCTION IF EXISTS `getInitial`;

CREATE DEFINER = `dowadmin`@`%` FUNCTION `getInitial`(str VARCHAR(255) CHARSET utf8)
RETURNS varchar(255)
READS SQL DATA
BEGIN
   DECLARE V_RETURN VARCHAR(200);
   DECLARE V_FIRST_CHAR VARCHAR(2);
   SET V_FIRST_CHAR = UPPER(LEFT(str,1));
   SET V_RETURN = V_FIRST_CHAR;
   IF LENGTH( V_FIRST_CHAR) <> CHARACTER_LENGTH( V_FIRST_CHAR ) THEN
   SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(str 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');
           elseif V_FIRST_CHAR in ('A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z')
   THEN SET V_RETURN = V_RETURN;
   ELSE SET V_RETURN = '#';
END IF;
   RETURN IFNULL(V_RETURN,'#');