MySQL Chinese converted to phonetic function

Source: Internet
Author: User
Tags character set

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

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

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

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

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.