--获取汉字拼音首字母的函数其一
DROP FUNCTION IF EXISTS `GET_FIRST_PINYIN_CHAR `;
DELIMITER ;;
CREATE FUNCTION `GET_FIRST_PINYIN_CHAR `(PARAM VARCHAR(255) ) RETURNS VARCHAR (2) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE V_RETURN VARCHAR (255) ;
DECLARE V_FIRST_CHAR VARCHAR(2 );
SET V_FIRST_CHAR = UPPER(LEFT(PARAM,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(PARAM USING gbk),1 )),16 ,10 ),
0 xB0A1,0 xB0C5,0 xB2C1,0 xB4EE,0 xB6EA,0 xB7A2,0 xB8C1,0 xB9FE,0 xBBF7,
0 xBFA6,0 xC0AC,0 xC2E8,0 xC4C3,0 xC5B6,0 xC5BE,0 xC6DA,0 xC8BB,
0 xC8F6,0 xCBFA,0 xCDDA,0 xCEF4,0 xD1B9,0 xD4D1),
'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 ;
RETURN V_RETURN;
END
;;
DELIMITER ;
DROP FUNCTION IF EXISTS `GET_FIRST_PINYIN_CHAR` ;
DELIMITER ;;
CREATE FUNCTION `GET_FIRST_PINYIN_CHAR` (P_NAME VARCHAR (255 )) RETURNS varchar (255 ) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE V_RETURN VARCHAR (255 );
DECLARE V_BOOL INT DEFAULT 0;
DECLARE FIRST_VARCHAR VARCHAR(1);
SET FIRST_VARCHAR = left (CONVERT(P_NAME USING gbk),1 );
SELECT FIRST_VARCHAR REGEXP '[a-zA-Z]' INTO V_BOOL;
IF V_BOOL = 1 THEN
SET V_RETURN = FIRST_VARCHAR;
ELSE
SET V_RETURN = ELT(INTERVAL (CONV(HEX(left (CONVERT(P_NAME 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 ;
RETURN V_RETURN;
END
; ;
DELIMITER ;