SQL Chinese character to PinYin function) + converts Chinese characters in the table to PinYin

[Leaf function sharing 290] Chinese character to PinYin function classification: SQL function sharing series people read comments (0) Add to favorites report/* response function: fn_getpinyin description: conversion of Chinese characters to PinYin (non-data table version): DBO. fn_getpinyin ('People's Republic of China') = zhonghuarenmingongheguo Author: Liu Xiangyu (adapted: Tony) blog: Creating functions */-- create functions 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 -- test example select DBO. fn_getpinyin ('Welcome to the leaf blog') -- run the result/* Huan Ying Fang Wen Ye Zi de Bo ke */This article from the csdn blog, reprinted please indicate the source: http://blog.csdn.net/htl258/archive/2009/09/18/4567350.aspx

　　

/*

Convert Chinese characters in the data table to PinYin

*/

declare @id intdeclare @name varchar(50)declare @pinyin varchar(50)set @id=2while @id<81beginselect @name=LoginName from User_User where User_UserID=@idSELECT @pinyin=dbo.fn_GetPinyin(@name)select @pinyinupdate User_User set EnglishName=@pinyin,PinyinName=@pinyin where User_UserID=@idset @id=@id+1endselect * from User_UserUpdate User_User set EnglishName=replace(EnglishName,' ',''),PinyinName=replace(PinyinName,' ','')