MySQL string to get the first letter of the Chinese Phonetic Alphabet by function

DELIMITER $$

DROP FUNCTION IF EXISTS ' fun_getpy ' $$

CREATE FUNCTION ' his '. ' Fun_getpy '
(In_string VARCHAR (21845))
RETURNS VARCHAR (21845) CHARSET UTF8
BEGIN
#截取字符串, each time the truncated string is stored in the variable, the initial function parameter in_string value
DECLARE tmp_str VARCHAR (21845) CHARSET gbk DEFAULT ';
#tmp_str的长度
DECLARE Tmp_len SMALLINT DEFAULT 0;
#tmp_str的长度
DECLARE Tmp_loc SMALLINT DEFAULT 0;
#截取字符, each left (tmp_str,1) return value is stored in the variable
DECLARE Tmp_char VARCHAR (2) CHARSET GBK DEFAULT ';
#结果字符串
DECLARE tmp_rs VARCHAR (21845) CHARSET gbk DEFAULT ';
#拼音字符, storing the phonetic first character corresponding to a single Chinese character
DECLARE tmp_cc VARCHAR (2) CHARSET GBK DEFAULT ';
#初始化, assign in_string to Tmp_str.
SET tmp_str = in_string;
#初始化长度
SET Tmp_len = LENGTH (TMP_STR);
#如果被计算的tmp_str长度大于0则进入该while
While Tmp_len > 0 do
#获取tmp_str最左端的首个字符, note that this is the first character to be obtained, which may or may not be a Chinese character.
SET Tmp_char = left (tmp_str,1);
#左端首个字符赋值给拼音字符
SET tmp_cc = Tmp_char;
#获取字符的编码范围的位置, in order to confirm that the first letter of Chinese pinyin is that one
SET Tmp_loc=interval (CONV (HEX (Tmp_char), 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);
#判断左端首个字符是多字节还是单字节字符, if multibyte is considered a kanji and is obtained as the following pinyin, if the single byte is not processed. If it is a multibyte character but is not within the corresponding encoding range, that is, the corresponding is not an uppercase letter is also not processed, so that the number or special characters will remain the same
IF (LENGTH (Tmp_char) >1 and Tmp_loc>0 and tmp_loc<24) then
#获得汉字拼音首字符
SELECT ELT (tmp_loc, ' A ', ' B ', ' C ', ' D ', ' E ', ' F ', ' G ', ' H ', ' J ', ' K ', ' L ', ' M ', ' N ', ' O ', ' P ', ' Q ', ' R ', ' S ', ' T ', ' W ', ' X ', ' Y ', ' Z ') into TMP_CC;
END IF;
IF ASCII (TMP_CC) >64 and ASCII (TMP_CC) <91
Then
#将当前tmp_str左端首个字符拼音首字符与返回字符串拼接
SET tmp_rs = CONCAT (TMP_RS,TMP_CC);
END IF;
#将tmp_str左端首字符去除
SET tmp_str = SUBSTRING (tmp_str,2);
#计算当前字符串长度
SET Tmp_len = LENGTH (TMP_STR);
END while;
#返回结果字符串
RETURN tmp_rs;
end$$

DELIMITER;

Test:

Select Fun_getpy (' September 2016, EHR, EMR, plus Chinese English punctuation \>\,\/,. ；‘)

MySQL string to get the first letter of the Chinese Phonetic Alphabet by function