MySQL user-defined functions for converting Chinese characters to Pinyin and examples (first letter)

FristPinyin: This function is used to convert the first Chinese character of a Chinese string into a pinyin letter (for example, "Chinese"-> Z)
Copy codeThe Code is as follows:
Create function 'fristpinin' (P_NAME VARCHAR (255) RETURNS varchar (255) CHARSET utf8
BEGIN
DECLARE V_RETURN VARCHAR (255 );
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, 0 xCBFA, 0 xCDDA, 0xCEF4, 0xD1B9, 0xD4D1 ),
'A', 'B', 'C', 'D', 'E', 'E', 'F', 'G', 'h', 'J', 'k ', 'l', 'M', 'n', 'O', 'P', 'Q', 'R', 's', 't', 'w ', 'X', 'y', 'z ');
RETURN V_RETURN;
END
Pinyin: This function connects each Chinese character string corresponding to the pinyin mother (for example, "Chinese"-> ZGR)
Copy codeThe Code is as follows:
Create function 'pinin' (P_NAME VARCHAR (255) RETURNS varchar (255) CHARSET utf8
BEGIN
DECLARE V_COMPARE VARCHAR (255 );
DECLARE V_RETURN VARCHAR (255 );
Declare I INT;

Set I = 1;
SET V_RETURN = '';
While I <LENGTH (P_NAME) do
SET V_COMPARE = SUBSTR (P_NAME, I, 1 );
IF (V_COMPARE! = '') THEN
# SET V_RETURN = CONCAT (V_RETURN, ',', V_COMPARE );
SET V_RETURN = CONCAT (V_RETURN, fristPinyin (V_COMPARE ));
# SET V_RETURN = fristPinyin (V_COMPARE );
End if;
Set I = I + 1;
End while;

IF (ISNULL (V_RETURN) or V_RETURN = '') THEN
SET V_RETURN = P_NAME;
End if;

RETURN V_RETURN;
END
Example:
Copy codeThe Code is as follows:
Mysql> select p. province, fristPinyin (p. province), pinyin (p. province) from province p;
+ ------------------ + ------------------------- + -------------------- +
| Province | fristPinyin (p. province) | pinyin (p. province) |
+ ------------------ + ------------------------- + -------------------- +
| Beijing | B | BJS |
| Tianjin | T | TJS |
| Hebei Province | H | Harvard |
| Shanxi Province | S | SXS |
| Inner Mongolia | N | NMGZZQ |
+ ------------------ + ------------------------- + -------------------- +
5 rows in set