SQL function: converts Chinese characters to PinYin

SQL database
Customize a function
Write the following code

The function is to get the first letter of the Chinese character pinyin

As follows:

Create function fun_getPY (@ str nvarchar (4000) returns nvarchar (4000) asbegindeclare @ word nchar (1), @ PY nvarchar (4000) set @ PY = ''while len (@ str)> 0 beginset @ word = left (@ str, 1) -- if it is not a Chinese character, returns the original character set @ PY = @ PY + (case when unicode (@ word) between 19968 and 19968 + 20901 then (select top 1 PY from (select 'A' as PY, N 'hour' as wordunion all select 'B', n' 'union all select 'C', n' 'Union all select 'D ', N 'hour' union all select 'E', N 'hangzhou' union all select 'F', N 'hangzhou' union all select 'G ', N 'hour' union all select 'h', N 'hangzhou' union all select 'J', N 'hangzhou' union all select 'k ', N 'hour' union all select 'l', N 'hour' union all select 'M', N 'hour' union all select 'n ', N 'hour' union all select 'O', N 'hour' union all select 'P', N 'exposure 'Union all select 'Q ', N 'hour' union all select 'R', N 'region' union all select's ', N 'region' union all select 'T ', N 'weight' union all select 'w', N 'weight' union all select 'x', N 'weight' union all select 'y ', N 'hour' union all select 'Z', n' then ') T where word >=@ word collate Chinese_PRC_CS_AS_KS_WS order by py asc) else @ word end) set @ str = right (@ str, len (@ str)-1) endreturn @ PYend

-- Function call instance:
Select dbo. fun_getPY ('People's Republic of China ')
All results are: ZHRMGHG

/* ------------------------------------------------------------- Function: fn_GetPinyin Description: Convert Chinese characters to PinYin (non-data table version) Use: dbo. fn_GetPinyin ('People's Republic of China') = zhonghuarenmingongheguo Author: liuxiangyu (adapted: Tony) blog: http://hi.baidu.com/liuxiangyu ------------------------------------------------------------- */IF OBJECT_ID ('[fn_GetPinyin is not null drop function [fn_GetPinyin] GO create function [dbo]. [fn_GetPinyin] (@ words nvarchar (2000) returns varchar (8000) as begin declare @ word nchar (1) declare @ pinyin varchar (8000) declare @ I int declare @ words_len int declare @ unicode int set @ I = 1 set @ words = ltrim (rtrim (@ words) set @ words_len = len (@ words) while (@ I <= @ words_len) -- cyclically take the character begin set @ word = substring (@ words, @ I, 1) set @ unicode = unicode (@ word) set @ pinyin = ISNULL (@ pinyin + SPACE (1), '') + (case when unicode (@ word) between 19968 and 19968 + 20901 then (select top 1 py from (select 'A' as py, n' then 'as word union all select 'ai ', N 'hangzhou' union all select 'A', N 'hangzhou' union all select 'ang ', N 'hangzhou' union all select 'ao ', N 'hangzhou' union all select 'BA ', N 'hangzhou' union all select 'bai', N 'hangzhou' -- divide each union all select 'Ban ', N 'apos 'union all select 'bang', N 'hangzhou' union all select 'Bao', N 'hangzhou' union all select 'bei ', N 'hangzhou' union all select 'ben', N 'hangzhou' union all select 'beng', N 'hangzhou' union all select 'bi ', N 'region' union all select 'bian ', n' becomes 'Union all select 'biao', n' then 'Union all select 'bie ', N 'hangzhou' union all select 'bin', N 'hangzhou' union all select 'bin', N 'hangzhou' union all select 'bo ', N 'stopped' union all select 'Bu ', N 'stopped' union all select 'CA', N 'stopped' union all select 'cai ', N 'hangzhou' -- Except union all select 'can ', N 'hangzhou' union all select 'cang', N 'hangzhou' union all select 'cao ', N 'hangzhou' union all select 'ce ', N 'hangzhou' union all select 'cen', N 'hangzhou' union all select 'ceng ', N 'taobao' -- there are two rows except union all select 'cha ', N 'hangzhou' union all select 'chai', N 'hangzhou' union all select 'chan ', N 'weight' union all select 'Chang', N 'weight' union all select 'chun', N 'weight' union all select 'ch ', N 'hangzhou' union all select 'chen ', N 'hangzhou' union all select 'cheng', N 'weight' union all select 'chi ', N 'hangzhou' union all select 'chun', N 'hangzhou' union all select 'chou', N 'hangzhou' union all select 'chunu ', N 'hangzhou' union all select 'chuai', N 'hangzhou' union all select 'shanghai', N 'hangzhou' union all select 'shanghai ', N 'taobao' union all select 'chui ', N 'taobao' union all select 'chun', N 'dump' union all select 'chuo ', N 'hour' union all select 'cies', N 'hangzhou' -- Except union all select 'cong', N 'hangzhou' union all select 'cou ', N 'hour' union all select 'cu ', N 'hour' union all select 'cuanc', N 'hour' union all select 'cui ', N 'hangzhou' union all select 'cun', N 'hangzhou' union all select 'cuo ', N 'hangzhou' union all select 'da ', N 'hangzhou' union all select 'day', N 'hangzhou' union all select 'dan', N 'hangzhou' union all select 'dang ', N 'hour' union all select 'dao ', N 'hangzhou' union all select 'D', N' 'Union all select 'den ', N 'hangzhou' union all select 'deng', N 'hangzhou' union all select 'di', N 'hangzhou' union all select 'dia ', N 'region' union all select 'dian', N 'region' union all select 'diao', N 'region' union all select 'di ', N 'weight' -- Except union all select 'ding', N 'weight' union all select 'diu', N 'weight' union all select 'dong ', N 'weight' union all select 'dou', N 'weight' union all select 'du', N 'weight' union all select 'duany ', N 'taobao' -- Except union all select 'dui', N 'region' union all select 'dun', N 'region' union all select 'duo ', N 'hour' union all select 'E', N 'hour' union all select 'en', N 'press 'Union all select 'eng ', N 'hangzhou' union all select 'er', N 'hangzhou' union all select 'fa ', N 'hangzhou' union all select 'fan ', N 'hangzhou' union all select 'fang ', N 'put 'Union all select 'fei', N 'hangzhou' union all select 'fen ', N 'hangzhou' union all select 'feng', N 'hangzhou' union all select 'fo', N 'hangzhou' union all select 'fou ', N 'hangzhou' union all select 'fu', N 'hangzhou' -- Except union all select 'ga ', N 'hangzhou' union all select 'gai ', N 'hangzhou' union all select 'gan ', N 'hangzhou' union all select 'gang', N 'hangzhou' union all select 'gao ', N 'weight' union all select 'ge', N 'weight' union all select 'gei', N 'to 'Union all select 'gen ', N 'hangzhou' union all select 'geng', N 'hangzhou' -- except when there are other union all select 'gong ', N 'hangzhou' -- Except union all select 'gou', N 'hangzhou' union all select 'GU', N 'hangzhou' union all select 'gua ', N 'weight' union all select 'guai', N 'weight' union all select 'guany', N 'weight' union all select 'guang ', N 'weight' union all select 'GU', N 'weight' union all select 'gun', N 'weight' union all select 'guo ', N 'hangzhou' union all select 'ha', N 'ha' union all select 'hai ', N 'hangzhou' union all select 'Han ', N 'hangzhou' union all select 'hangang ', N 'hangzhou' union all select 'hao', N 'hangzhou' union all select 'he ', N 'weight' union all select 'hei', N 'weight' union all select 'weight', N 'hate 'Union all select 'heng ', N 'hangzhou' -- Except union all select 'Hong ', N 'hangzhou' union all select 'hou', N 'hangzhou' union all select 'hu ', N 'shanghai' union all select 'hua', N 'shanghai' union all select 'huai', N 'shanghai' union all select 'huan ', N 'hour' union all select 'huangt', N 'hangzhou' union all select 'hui', N 'hangzhou' union all select 'hun ', N 'hour' union all select 'huo', N 'hour' union all select 'ji ', N 'hour' union all select 'jia ', N 'weight' union all select 'jian ', N 'weight' union all select 'jiang', N 'weight' union all select 'jiao ', N 'weight' union all select 'jie', N 'weight' union all select 'jin', N 'weight' union all select 'jing ', N 'weight' union all select 'jiong ', N 'weight' union all select 'jiu', N 'weight' union all select 'ju ', N 'hangzhou' union all select 'juany', N 'hangzhou' union all select 'jue ', N 'hangzhou' union all select 'jun ', N 'weight' union all select 'ka ', N 'weight' union all select 'Kai', N 'weight' -- except union all select 'kan ', N 'hangzhou' union all select 'hangzhou', N 'hangzhou' union all select 'kakao', N 'hangzhou' union all select 'ke ', N 'hangzhou' union all select 'ken', N 'hangzhou' union all select 'keng', N 'hangzhou' -- aggregate union all select 'Kong ', N 'hangzhou' union all select 'kou', N 'hangzhou' union all select 'ku ', N 'hangzhou' union all select 'kua ', N 'weight' union all select 'kuai', N 'weight' union all select 'kuance', N 'weight' union all select 'kuang ', N 'weight' union all select 'kui ', N 'weight' union all select 'kun', N 'weight' union all select 'kuo ', N 'hour' union all select 'La ', N 'hangzhou' union all select 'lai', N 'hangzhou' union all select 'lan ', N 'hour' union all select 'lang ', N 'hangzhou' union all select 'lao', N 'hangzhou' union all select 'le ', N 'weight' union all select 'lei', N 'weight' -- Except union all select 'leng', N 'weight' union all select 'lil ', N 'weight' union all select 'lia ', N 'weight' union all select 'liany', N 'weight' union all select 'liang ', N 'delimiter' union all select 'liao', n' knows 'Union all select 'lie ', n' then 'Union all select 'lin ', N 'running' -- perform union all select 'ler', N 'running' union all select 'Liu ', N '?' -- except when the union all select 'long', N 'Then 'Union all select 'lou', N 'Then 'Union all select 'lu ', N 'hour' union all select 'lv ', N 'hangzhou' union all select 'luand', N 'hangzhou' union all select 'lue ', N 'hangzhou' union all select 'lun', N 'After 'Union all select 'luo', N 'hangzhou' union all select 'M ', n'well 'Union all select 'mai', n' comment 'Union all select 'man ', n' comment 'Union all select 'mang ', N 'hangzhou' union all select 'mao ', N 'hangzhou' union all select 'me', N 'hangzhou' -- except union all select 'Mei ', N 'hangzhou' union all select 'men ', N 'hangzhou' union all select 'meng', N 'hangzhou' -- Except union all select 'mi ', N 'region' union all select 'mian', N 'region' union all select 'miao', N 'region' union all select 'mi ', N 'weight' -- Except union all select 'Min', N 'weight' union all select 'ming', N 'weight' union all select 'miu ', N 'hour' union all select 'M', N 'hour' -- Except union all select 'mou', N 'hour' -- Except union all select 'mu ', N 'hour' union all select 'A', N 'hangzhou' union all select 'nai', N 'hangzhou' union all select 'nan ', N 'hangzhou' union all select 'nang ', N 'hangzhou' union all select 'nao', N 'hangzhou' union all select 'ne ', N ', 'Union all select 'nei', N 'nee' -- tender except union all select 'nen', N 'none' union all select 'neng ', n'neng' -- well, there are two rows except union all select 'ni', N 'hour' union all select 'niance', N 'hour' union all select 'niang ', N 'weight' union all select 'niao', N 'ure' union all select 'ni', N 'weight' union all select 'nin ', N 'hour' union all select 'ning ', N 'hangzhou' union all select 'niu', N 'hangzhou' union all select 'nong ', N 'hour' union all select 'nou', N 'hangzhou' union all select 'nu ', N 'hangzhou' union all select 'nv ', N 'hangzhou' union all select 'nue ', N 'hangzhou' union all select 'nuance', N 'hangzhou' -- unless otherwise used union all select 'nuo ', N 'hour' union all select 'O', N 'hour' -- Except except when union all select 'ou ', N 'hour' union all select 'Pa ', N 'hangzhou' union all select 'pai ', N 'hangzhou' -- Except union all select 'pan', N 'hangzhou' union all select 'pang ', N 'fat' union all select 'Pao', N 'weight' union all select 'pei ', N 'weight' union all select 'pen ', N 'hangzhou' union all select 'peng', N 'hangzhou' -- each vertex has a union all select 'pi', N 'weight' union all select 'pian ', N 'weight' union all select 'piao', N 'weight' union all select 'pie', N 'weight' union all select 'pin ', N 'hire 'Union all select 'ping', N 'hangzhou' union all select 'po', N 'water' union all select 'pou ', N 'delimiters -- Except union all select 'put', n' exposes 'Union all select 'qi ', n' then 'Union all select 'qia ', N 'ili' union all select 'Qian ', N 'hangzhou' union all select 'qiang', N 'hangzhou' -- except when union all select 'qiao ', N 'weight' union all select 'qie', N 'weight' union all select 'qin', N 'weight' union all select 'Qing ', N 'hangzhou' union all select 'qiong ', N 'hangzhou' union all select 'Qiu', N 'hangzhou' union all select 'qu ', N 'hour' union all select 'quany', N 'hangzhou' union all select 'que', N 'hangzhou' union all select 'qun ', N 'hour' union all select 'run', N 'hour' union all select 'Rang ', N 'let 'Union all select 'rao ', N 'hour' union all select 'Re', N 'hour' union all select 'ren', N 'hour' union all select 'reng ', N 'hangzhou' union all select 'ri ', N 'hangzhou' union all select 'rong', N 'hangzhou' union all select 'rou ', N 'delimiter' union all select 'ru ', N 'region' union all select 'rule', N 'region' union all select 'rui ', N 'run' union all select 'run', N 'running' -- Except union all select 'ruo', N 'except 'Union all select 'sa ', N 'hangzhou' -- Except union all select 'sai ', N 'hangzhou' -- Except union all select 'san', N 'hangzhou' union all select 'sang ', N 'hangzhou' union all select 'sao', N 'hangzhou' union all select 'se ', N 'hangzhou' -- coprocessor union all select 'sen ', N 'hangzhou' union all select 'seng', N 'hangzhou' -- Except union all select 'sha', N 'sha' union all select 'sha ', N 'hangzhou' union all select 'shance', N 'hangzhou' union all select 'shanghai', N 'hangzhou' union all select 'shao ', N 'hangzhou' union all select 'she', N 'hangzhou' union all select 'shen', N 'hangzhou' union all select 'sheng ', N 'hangzhou' union all select 'shi', N 'hangzhou' -- except when union all select 'shou', N 'hangzhou' union all select 'shu ', N 'shanghai' union all select 'shua ', N 'shanghai' union all select 'shuai', N 'shanghai' union all select 'shuance ', N 'hangzhou' union all select 'shanghai ', N 'hangzhou' union all select 'shanghai', N 'hangzhou' union all select 'si', N 'hangzhou' -- except when union all select 'song ', N 'hangzhou' union all select 'sou', N 'hangzhou' union all select 'su', N 'hangzhou' union all select 'suan ', 'N' calculate 'Union all select 'sui', n' then 'Union all select 'sun', n' then 'Union all select 'suo ', N 'Ta' union all select 'Ta', N 'Ta' -- Except union all select 'tai', N 'Ta' union all select 'Tan ', N 'hangzhou' union all select 'shanghai', N 'hangzhou' union all select 'Tao', N 'hangzhou' -- except union all select 'te ', N 'hangzhou' union all select 'teng', N 'hangzhou' -- except when union all select 'ti', N 'hangzhou' union all select 'tia ', N 'region' union all select 'tiao', N 'region' union all select 'tie ', N 'region' union all select 'ting ', N 'hangzhou' -- Except union all select 'tong', N 'hangzhou' union all select 'tou', N 'passthrough 'union all select 'tu ', N 'weight' union all select 'tuany', N 'weight' union all select 'tui ', N 'weight' union all select 'tn ', N 'region' union all select 'tuo', N 'region' union all select 'wa ', N 'region' union all select 'wai ', N 'hangzhou' union all select 'wan ', N 'hangzhou' union all select 'wang', N 'hangzhou' union all select 'wei ', N 'hour' union all select 'wen', N 'hangzhou' union all select 'engg', N 'hangzhou' union all select 'wo ', N 'hangzhou' union all select 'wu', N 'hangzhou' union all select 'Xi ', N 'hangzhou' union all select 'xia ', N 'weight' union all select 'xian ', N 'weight' union all select 'xiang', N 'weight' union all select 'xiao ', N 'weight' union all select 'xie', N 'weight' union all select 'xin ', N 'weight' union all select 'xing ', N 'weight' union all select 'xiong ', N 'weight' union all select 'xiu', N 'weight' union all select 'xu ', N 'hangzhou' union all select 'xuany', N 'hangzhou' union all select 'xue ', N 'hangzhou' union all select 'xun ', N 'weight' union all select 'ya ', N 'weight' union all select 'any', N 'weight' union all select 'yang ', N 'like 'Union all select 'yuno', N 'hangzhou' union all select 'day', N 'hangzhou' -- except when union all select 'yi ', N 'hour' union all select 'in', N 'hour' union all select 'ying', N 'hour' union all select 'yo ', N 'hangzhou' union all select 'yong ', N 'hangzhou' union all select 'you', N 'handler' union all select 'yu ', N 'hour' union all select 'yuanyuan ', N 'hangzhou' union all select 'yune', N 'hangzhou' union all select 'yun ', N 'weight' union all select 'za ', N 'weight' union all select 'zai', N 'weight' union all select 'za ', N 'hangzhou' union all select 'zang ', N 'hangzhou' union all select 'zao', N 'hangzhou' union all select 'ze ', N 'weight' union all select 'zei', N 'weight' union all select 'zn', N 'weight' union all select 'zeng ', N 'shanghai' union all select 'zha', N 'shanghai' union all select 'zhai', N 'shanghai' union all select 'zhan ', N 'hangzhou' union all select 'zhang', N 'hangzhou' union all select 'zhao', N 'hangzhou' union all select 'zhe ', N 'hangzhou' union all select 'zhen', N 'hangzhou' union all select 'zheng', N 'creden' union all select 'zhi ', N 'hangzhou' union all select 'zhong ', N 'hangzhou' union all select 'zhou', N 'hangzhou' union all select 'zhu ', N 'taobao' union all select 'zhua', N 'claw 'union all select 'zhuai', N 'taobao' union all select 'zhua ', N 'hour' union all select 'zhuang ', N 'hangzhou' union all select 'zhui', N 'hangzhou' union all select 'zhun ', N 'hour' union all select 'zhuo', N 'hour' union all select 'zi', N 'shanghai' -- except union all select 'zong ', N 'weight' union all select 'zou ', N 'weight' union all select 'zu', N 'weight' union all select 'zuan ', N 'taobao' union all select 'zui ', N 'taobao' union all select 'zun', N 'taobao' union all select 'zuo', N 'taobao ') t where word >=@ word collate Chinese_PRC_CS_AS_KS_WS order by word collate Chinese_PRC_CS_AS_KS_WS ASC) else @ word end) set @ I = @ I + 1 end return @ pinyin END GO SELECT dbo. fn_GetPinyin ('People's Republic of China')/* ------------------------------------- zuo zhong hua ren min gong he guo (1 line affected )*/