MySQL Chinese converted to phonetic function

Last Update:2017-01-13 Source: Internet

Author: User

Tags character set

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

Chinese Pinyin Whole spelling

The code is as follows

Copy Code

--Create a Chinese phonetic control temporary table
CREATE TABLE IF not EXISTS ' T_base_pinyin ' (
' Pin_yin_ ' varchar (255) CHARACTER SET GBK not NULL,
' Code_ ' int (one) is not NULL,
PRIMARY KEY (' Code_ ')
) Engine=innodb DEFAULT charset=latin1;
--Inserting data
INSERT into T_base_pinyin (pin_yin_,code_) VALUES ("a", 20319), ("AI", 20317), ("A", 20304), ("Ang", 20295), ("Ao", 2 0292), ("Ba", 20283), ("Bai", 20265), ("Ban", 20257), ("Bang", 20242), ("Bao", 20230), ("Bei", 20051), ("Ben", 20036), ("Beng ", 20032), (" Bi ", 20026), (" Bian ", 20002), (" Biao ", 19990), (" Bie ", 19986), (" Bin ", 19982), (" Bing ", 19976), (" Bo ", 19805), (" Bu ", 19784), (" Ca ", 19775), (" Cai ", 19774), (" Can ", 19763), (" Cang ", 19756), (" Cao ", 19751), (" Ce ", 19746), (" CEng ", 19741), ( "Cha", 19739, ("Chai", 19728), ("Chan", 19725), ("Chang", 19715), ("Chao", 19540), ("Che", 19531), ("Chen", 19525), ("Cheng ", 19515), (" Chi ", 19500), (" Chong ", 19484), (" Chou ", 19479), (" Chu ", 19467), (" Chuai ", 19289), (" Chuan ", 19288), (" Chuang ", 19281), ("Chui", 19275), ("Chun", 19270), ("Chuo", 19263), ("Ci", 19261), ("Cong", 19249), ("Cou", 19243), ("Cu", 19242), (" Cuan ", 19238), (" Cui ", 19235), (" Cun ", 19227), (" CuO ", 19224), (" Da ", 19218), (" Dai ", 19212), (" Dan ", 19038), (" Dang ", 19023) , ("DAO", 19018), ("De", 19006), ("Deng", 19003), ("Di", 18996), ("Dian", 18977), ("Diao", 18961), ("Die", 18952), ("Ding", 18783), ("Diu", 18774), ("Dong", 18773), ("Dou", 18763), ("Du" , 18756), ("Duan", 18741), ("DUI", 18735), ("Dun", 18731), ("Duo", 18722), ("E", 18710), ("en", 18697), ("ER", 18696), ("The FA", 18526), ("Fan", 18518), ("Fang", 18501), ("Fei", 18490), ("Fen", 18478), ("Feng", 18463), ("Fo", 18448), ("Fou", 18447), ("Fu ", 18446), (" Ga ", 18239), (" Gai ", 18237), (" Gan ", 18231), (" Gang ", 18220), (" Gao ", 18211), (" GE ", 18201), (" GEi ", 18184), (" Gen ", 18183), (" Geng ", 18181), (" Gong ", 18012), (" Gou ", 17997), (" Gu ", 17988), (" Gua ", 17970), (" Guai ", 17964), (" Guan ", 17961), ("Guang", 17950), ("GUI", 17947), ("Gun", 17931), ("Guo", 17928), ("Ha", 17922), ("Hai", 17759), ("Han", 17752), (" Hang ", 17733), (" Hao ", 17730), (" he ", 17721), (" Hei ", 17703), (" Hen ", 17701), (" Heng ", 17697), (" 17692 ", Hou), (" ", 17683 ), ("Hu", 17676), ("Hua", 17496), ("Huai", 17487), ("Huan", 17482), ("Huang", 17468), ("Hui", 17454), ("Hun", 17433), ("Huo", 17427), ("Ji", 17417), ("Jia", 17202), ("Jian", 17185), ("Jiang", 16983), ("Jiao", 16970), ("Jie", 16942), ("Jin", 16915), ("Jing", 16733), ("Jiong", 16708), ("JIU", 16706), ("Ju", 16689), ("Juan" , 16664), ("Jue", 16657), ("June", 16647), ("Ka", 16474), ("Kai", 16470), ("Kan", 16465), ("Kang", 16459), ("Kao", 16452), ("Ke" ", 16448), (" Ken ", 16433), (" Keng ", 16429), (" Kong ", 16427), (" Kou ", 16423), (" Ku ", 16419), (" Kua ", 16412), (" Kuai ", 16407), ( "Kuan", 16403), ("Kuang", 16401), ("Kui", 16393), ("Kun", 16220), ("Kuo", 16216), ("La", 16212), ("Lai", 16205), ("LAN", 16202), ("Lang", 16187), ("Lao", 16180), ("Le", 16171), ("Lei", 16169), ("Leng", 16158), ("Li", 16155), ("Lia", 15959), (" Lian ", 15958), (" Liang ", 15944), (" Liao ", 15933), (" Lie ", 15920), (" Lin ", 15915), (" Ling ", 15903), (" Liu ", 15889), (" Long ", 15878), ("Lou", 15707), ("Lu", 15701), ("LV", 15681), ("Luan", 15667), ("Lue", 15661), ("Lun", 15659), ("Luo", 15652), ("Ma", 15640), ("Mai", 15631), ("Man", 15625), ("Mang", 15454), ("Mao", 15448), ("Me", 15436), ("Mei", 15435), ("Men", 15419), (" Meng ", 15416), (" Mi ", 15408), (" Mian ", 15394), (" Miao ", 15385), (" Mie ", 15377), ("Min", 15375), ("Ming", 15369), ("Miu", 15363), ("Mo", 15362), ("MoU", 15183), ("Mu", 15180), ("NA", 15165), ("Nai", 15158), ("Nan", 15153), ("Nang", 15150), ("Nao", 15149), ("Ne", 15144), ("Nei", 15143), ("nen", 15141), ("Neng", 15140), ("Ni ", 15139), (" Nian ", 15128), (" Niang ", 15121), (" Niao ", 15119), (" Nie ", 15117), (" Nin ", 15110), (" Ning ", 15109), (" Niu ", 14941 ), ("Nong", 14937), ("Nu", 14933), ("NV", 14930), ("Nuan", 14929), ("Nue", 14928), ("Nuo", 14926), ("O", 14922), ("ou", 14921) , ("Pa", 14914), ("Pai", 14908), ("Pan", 14902), ("Pang", 14894), ("Pao", 14889), ("Pei", 14882), ("pen", 14873), ("Peng", 14871), ("PI", 14857), ("Pian", 14678), ("Piao", 14674), ("Pie", 14670), ("Pin", 14668), ("Ping", 14663), ("Po", 14654), ("Pu ", 14645), (" Qi ", 14630), (" QIA ", 14594), (" Qian ", 14429), (" Qiang ", 14407), (" Qiao ", 14399), (" Qie ", 14384), (" Qin ", 14379), ("Qing", 14368), ("Qiong", 14355), ("Qiu", 14353), ("Qu", 14345), ("Quan", 14170), ("que", 14159), ("Qun", 14151), ("ran", 14149), ("Rang", 14145), ("Rao", 14140), ("Re", 14137), ("Ren", 14135), ("Reng", 14125), ("Ri", 14123), ("Rong", 14122), ("Rou", 14112), ("Ru", 14109), ("Ruan", 14099), ("Rui", 14097), ("Run", 14094), ("Ruo", 14092), ("sa", 14090), ("Sai", 14087), ("San", 14083), ("sang", 13917), ("Sao", 13914), ("se", 13910), ("Sen" , 13907), ("Seng", 13906), ("Sha", 13905), ("Shai", 13896), ("Shan", 13894), ("Shang", 13878), ("Shao", 13870), ("she", 13859 ), ("Shen", 13847), ("Sheng", 13831), ("Shi", 13658), ("Shou", 13611), ("Shu", 13601), ("Shua", 13406), ("Shuai", 13404), (" Shuan ", 13400), (" Shuang ", 13398), (" Shui ", 13395), (" Shun ", 13391), (" Shuo ", 13387), (" Si ", 13383), (" song ", 13367), (" Sou " , 13359), ("Su", 13356), ("Suan", 13343), ("Sui", 13340), ("Sun", 13329), ("suo", 13326), ("Ta", 13318), ("Tai", 13147), ("Tan ", 13138), (" Tang ", 13120), (" Tao ", 13107), (" TE ", 13096), (" Teng ", 13095), (" Ti ", 13091), (" Tian ", 13076), (" Tiao ", 13068), ( "Tie", 13063), ("Ting", 13060), ("Tong", 12888), ("Tou", 12875), ("Tu", 12871), ("Tuan", 12860), ("Tui", 12858), ("Tun", 12852), ("Tuo", 12849), ("WA", 12838), ("Wai", 12831), ("Wan", 12829), ("Wang", 12812), ("Wei", 12802), ("Wen", 12607), ("Weng", 12597), ("Wo", 12594), ("WU", 12585), ("Xi", 12556), ("Xia", 12359 ), ("Xian", 12346), ("Xiang", 12320), ("Xiao", 12300), ("Xie", 12120), ("Xin", 12099), ("Xing", 12089), ("Xiong", 12074), (" Xiu ", 12067), (" Xu ", 12058), (" Xuan ", 12039), (" Xue ", 11867), (" Xun ", 11861), (" Ya ", 11847), (" Yan ", 11831), (" Yang ", 11798), ("Yao", 11781), ("Ye", 11604), ("Yi", 11589), ("Yin", 11536), ("Ying", 11358), ("Yo", 11340), ("Yong", 11339), ("For You", 11324) , ("Yu", 11303), ("Yuan", 11097), ("Yue", 11077), ("Yun", 11067), ("Za", 11055), ("Zai", 11052), ("Zan", 11045), ("Zang", 11041), ("Zao", 11038), ("Ze", 11024), ("Zei", 11020), ("Zen", 11019), ("Zeng", 11018), ("Zha", 11014), ("Zhai", 10838), (" Zhan ", 10832), (" Zhang ", 10815), (" Zhao ", 10800), (" Zhe ", 10790), (" Zhen ", 10780), (" Zheng ", 10764), (" Zhi ", 10587), (" Zhong " ", 10544), (" Zhou ", 10533), (" Zhu ", 10519), (" Zhua ", 10331), (" Zhuai ", 10329), (" Zhuan ", 10328), (" Zhuang ", 10322), (" Zhui ", 10315), ("Zhun", 10309), ("Zhuo", 10307), ("Zi", 10296), ("Zong", 10281), ("Zou", 10274), ("Zu", 10270), ("Zuan", 10262), ("Zui", 10260), ("Zun", 10256), ("Zuo", 10254);

--establishing phonetic function of Chinese character conversion
DROP FUNCTION IF EXISTS to_pinyin;
DELIMITER $
CREATE FUNCTION To_pinyin (NAME VARCHAR (255) CHARSET GBK)
RETURNS VARCHAR (255) CHARSET GBK
BEGIN
DECLARE Mycode INT;
DECLARE Tmp_lcode VARCHAR (2) CHARSET GBK;
DECLARE Lcode INT;

DECLARE Tmp_rcode VARCHAR (2) CHARSET GBK;
DECLARE Rcode INT;

DECLARE mypy VARCHAR (255) CHARSET GBK DEFAULT ';
DECLARE LP INT;

SET mycode = 0;
SET LP = 1;

SET name = HEX (name);

While LP < LENGTH (NAME) does

SET Tmp_lcode = SUBSTRING (NAME, LP, 2);
SET Lcode = CAST (ASCII (Unhex (Tmp_lcode)) as UNSIGNED);
SET Tmp_rcode = SUBSTRING (NAME, LP + 2, 2);
SET Rcode = CAST (ASCII (Unhex (Tmp_rcode)) as UNSIGNED);
IF lcode > 128 THEN
SET mycode =65536-lcode * 256-rcode;
SELECT CONCAT (mypy,pin_yin_) into Mypy from T_base_pinyin WHERE code_ >= ABS (mycode) Order by Code_ ASC LIMIT 1;
SET LP = LP + 4;
ELSE
SET mypy = CONCAT (Mypy,char (CAST (ASCII (Unhex (SUBSTRING (NAME, LP, 2)) as UNSIGNED));
SET LP = LP + 2;
End IF;
End while;
Return LOWER (Mypy);
End;
$
DELIMITER;
Usage:

Select To_pinyin (' Test ')
Output results:

To_pinyin (' Test ')
Ceshi

Example 2 Chinese character pinyin function

Fristpinyin: This function converts the first character of a Chinese string into a pinyin (for example, "Chinese"->z)

The code is as follows

Copy Code

CREATE FUNCTION ' Fristpinyin ' (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,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 ';
return v_return;
End
Pinyin: This function is to associate a Chinese string with each phonetic mother (for example, "Chinese"->zgr)

CREATE FUNCTION ' Pinyin ' (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:

Mysql> Select P.province, Fristpinyin (p.province), pinyin (P.province) from province p;
+------------------+-------------------------+--------------------+
| Province | Fristpinyin (p.province) | Pinyin (p.province) |
+------------------+-------------------------+--------------------+
| Beijing | B | BJS |
| Tianjin City | T | TJS |
| Hebei Province | H | HBS |
| Shanxi Province | S | SXS |
| Inner Mongolia | N | Nmgzzq |
+------------------+-------------------------+--------------------+
5 rows in Set

Original from: 9958.pw thank stationmaster, by several articles finishing

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More