MSSQL汉字转拼音函数实现语句

/* -------------------------------------------------------------
函数: fn_GetPinyin
描述: 汉字转拼音(无数据表版)
使用: dbo.fn_GetPinyin('中华人民共和国') = zhonghuarenmingongheguo
作者: 流香羽 (改编：Tony)
------------------------------------------------------------- */

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) --循环取字符
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'??' as word
union all select 'ai',N'?a'
union all select 'an',N'黯'
union all select 'ang',N'?l'
union all select 'ao',N'?'
union all select 'ba',N'??'
union all select 'bai',N'?B' --?v?漠B
union all select 'ban',N'瓣'
union all select 'bang',N'?^'
union all select 'bao',N'?t'
union all select 'bei',N'鐾'
union all select 'ben',N'?'
union all select 'beng',N'?a'
union all select 'bi',N'??'
union all select 'bian',N'?'
union all select 'biao',N'?B'
union all select 'bie',N'??'
union all select 'bin',N'?W'
union all select 'bing',N'?h'
union all select 'bo',N'?N'
union all select 'bu',N'簿'
union all select 'ca',N'??'
union all select 'cai',N'?k' --?n?k
union all select 'can',N'?|'
union all select 'cang',N'?'
union all select 'cao',N'??'
union all select 'ce',N'?u'
union all select 'cen',N'?'
union all select 'ceng',N'?u' --???K?e?u
union all select 'cha',N'?'
union all select 'chai',N'??'
union all select 'chan',N'?'
union all select 'chang',N'?o'
union all select 'chao',N'?e'
union all select 'che',N'??'
union all select 'chen',N'?'
union all select 'cheng',N'秤'
union all select 'chi',N'?u'
union all select 'chong',N'?|'
union all select 'chou',N'??'
union all select 'chu',N'矗'
union all select 'chuai',N'踹'
union all select 'chuan',N'?E'
union all select 'chuang',N'??'
union all select 'chui',N'?q'
union all select 'chun',N'蠢'
union all select 'chuo',N'?W'
union all select 'ci',N'??' --?n??
union all select 'cong',N'?'
union all select 'cou',N'?'
union all select 'cu',N'?'
union all select 'cuan',N'爨'
union all select 'cui',N'?'
union all select 'cun',N'?v'
union all select 'cuo',N'?e'
union all select 'da',N'?\'
union all select 'dai',N'?^'
union all select 'dan',N'?'
union all select 'dang',N'?W'
union all select 'dao',N'纛'
union all select 'de',N'的'
union all select 'den',N'?Y'
union all select 'deng',N'?'
union all select 'di',N'?E'
union all select 'dia',N'嗲'
union all select 'dian',N'?'
union all select 'diao',N'?S'
union all select 'die',N'??' --???
union all select 'ding',N'?r'
union all select 'diu',N'?A'
union all select 'dong',N'?'
union all select 'dou',N'?a'
union all select 'du',N'蠹'
union all select 'duan',N'??' --?f??
union all select 'dui',N'?m'
union all select 'dun',N'?v'
union all select 'duo',N'?z'
union all select 'e',N'?{'
union all select 'en',N'摁'
union all select 'eng',N'?E'
union all select 'er',N'??'
union all select 'fa',N'?'
union all select 'fan',N'?~'
union all select 'fang',N'放'
union all select 'fei',N'?]'
union all select 'fen',N'?a'
union all select 'feng',N'?'
union all select 'fo',N'??'
union all select 'fou',N'?]'
union all select 'fu',N'?g' --?v?g
union all select 'ga',N'?p'
union all select 'gai',N'?y'
union all select 'gan',N'??'
union all select 'gang',N'??'
union all select 'gao',N'?'
union all select 'ge',N'??'
union all select 'gei',N'?o'
union all select 'gen',N'?j'
union all select 'geng',N'??' --????????
union all select 'gong',N'??' --?粟C???
union all select 'gou',N'?'
union all select 'gu',N'?'
union all select 'gua',N'?'
union all select 'guai',N'?s'
union all select 'guan',N'?}'
union all select 'guang',N'??'
union all select 'gui',N'?i'
union all select 'gun',N'?'
union all select 'guo',N'?B'
union all select 'ha',N'哈'
union all select 'hai',N'?'
union all select 'han',N'?['
union all select 'hang',N'沆'
union all select 'hao',N'??'
union all select 'he',N'?g'
union all select 'hei',N'??'
union all select 'hen',N'恨'
union all select 'heng',N'??' --????
union all select 'hong',N'?\'
union all select 'hou',N'?c'
union all select 'hu',N'?I'
union all select 'hua',N'?s'
union all select 'huai',N'?|'
union all select 'huan',N'?d'
union all select 'huang',N'?w'
union all select 'hui',N'?'
union all select 'hun',N'?'
union all select 'huo',N'??'
union all select 'ji',N'?K'
union all select 'jia',N'??'
union all select 'jian',N'?'
union all select 'jiang',N'?'
union all select 'jiao',N'?'
union all select 'jie',N'?T'
union all select 'jin',N'??'
union all select 'jing',N'?'
union all select 'jiong',N'?W'
union all select 'jiu',N'??'
union all select 'ju',N'??'
union all select 'juan',N'?\'
union all select 'jue',N'?'
union all select 'jun',N'?h'
union all select 'ka',N'?l'
union all select 'kai',N'?f' --?b?f
union all select 'kan',N'?'
union all select 'kang',N'?`'
union all select 'kao',N'?'
union all select 'ke',N'?S'
union all select 'ken',N'?y'
union all select 'keng',N'?H' --?|?g?{???]
union all select 'kong',N'?W'
union all select 'kou',N'?d'
union all select 'ku',N'??'
union all select 'kua',N'?g'
union all select 'kuai',N'?d'
union all select 'kuan',N'?U'
union all select 'kuang',N'?k'
union all select 'kui',N'?^'
union all select 'kun',N'?'
union all select 'kuo',N'?i'
union all select 'la',N'?B'
union all select 'lai',N'?['
union all select 'lan',N'?h'
union all select 'lang',N'?}'
union all select 'lao',N'?~'
union all select 'le',N'?E'
union all select 'lei',N'?' --嘞?
union all select 'leng',N'?'
union all select 'li',N'??'
union all select 'lia',N'?z'
union all select 'lian',N'?~'
union all select 'liang',N'?y'
union all select 'liao',N'?t'
union all select 'lie',N'?v'
union all select 'lin',N'?`' --?`拎
union all select 'ling',N'??'
union all select 'liu',N'咯' --?F?M?蘅?
union all select 'long',N'?L'
union all select 'lou',N'?U'
union all select 'lu',N'氇'
union all select 'lv',N'?r'
union all select 'luan',N'?y'
union all select 'lue',N'?^'
union all select 'lun',N'?'
union all select 'luo',N'?w'
union all select 'ma',N'嘛'
union all select 'mai',N'?A'
union all select 'man',N'?p'
union all select 'mang',N'?'
union all select 'mao',N'?x'
union all select 'me',N'?Z' --?Z??
union all select 'mei',N'??'
union all select 'men',N'??'
union all select 'meng',N'?D' --?W?_
union all select 'mi',N'?]'
union all select 'mian',N'?I'
union all select 'miao',N'?R'
union all select 'mie',N'?x' --?x??
union all select 'min',N'?'
union all select 'ming',N'?'
union all select 'miu',N'?'
union all select 'mo',N'耱' --耱?i
union all select 'mou',N'?E' --?E?w
union all select 'mu',N'??'
union all select 'na',N'?'
union all select 'nai',N'?r'
union all select 'nan',N'?R'
union all select 'nang',N'?Q'
union all select 'nao',N'?'
union all select 'ne',N'呢'
union all select 'nei',N'??' --嫩??
union all select 'nen',N'嫩'
union all select 'neng',N'能' --?嗯??G??
union all select 'ni',N'??'
union all select 'nian',N'?'
union all select 'niang',N'?'
union all select 'niao',N'脲'
union all select 'nie',N'?'
union all select 'nin',N'??'
union all select 'ning',N'??'
union all select 'niu',N'?'
union all select 'nong',N'?P'
union all select 'nou',N'?k'
union all select 'nu',N'?x'
union all select 'nv',N'衄'
union all select 'nue',N'??'
union all select 'nuan',N'?\' --??Q?\?G
union all select 'nuo',N'??'
union all select 'o',N'?M' --???j?M
union all select 'ou',N'?a'
union all select 'pa',N'?'
union all select 'pai',N'?s' --?W?s
union all select 'pan',N'?'
union all select 'pang',N'胖'
union all select 'pao',N'?^'
union all select 'pei',N'?\'
union all select 'pen',N'??'
union all select 'peng',N'??' --????C?n??
union all select 'pi',N'?G'
union all select 'pian',N'?_'
union all select 'piao',N'?G'
union all select 'pie',N'??'
union all select 'pin',N'聘'
union all select 'ping',N'?O'
union all select 'po',N'魄'
union all select 'pou',N'?R' --??R
union all select 'pu',N'曝'
union all select 'qi',N'?'
union all select 'qia',N'髂'
union all select 'qian',N'?y'
union all select 'qiang',N'??' --??凯?
union all select 'qiao',N'?N'
union all select 'qie',N'?]'
union all select 'qin',N'?C'
union all select 'qing',N'??'
union all select 'qiong',N'??'
union all select 'qiu',N'糗'
union all select 'qu',N'?Y'
union all select 'quan',N'??'
union all select 'que',N'?]'
union all select 'qun',N'??'
union all select 'ran',N'?L'
union all select 'rang',N'?'
union all select 'rao',N'?@'
union all select 're',N'??'
union all select 'ren',N'?'
union all select 'reng',N'?'
union all select 'ri',N'?_'
union all select 'rong',N'?\'
union all select 'rou',N'?]'
union all select 'ru',N'?J'
union all select 'ruan',N'?O'
union all select 'rui',N'??'
union all select 'run',N'??' --???
union all select 'ruo',N'?U'
union all select 'sa',N'??' --???
union all select 'sai',N'?' --??
union all select 'san',N'?d'
union all select 'sang',N'??'
union all select 'sao',N'?'
union all select 'se',N'?o' --?S?{
union all select 'sen',N'?d'
union all select 'seng',N'?L' --?~?L
union all select 'sha',N'霎'
union all select 'shai',N'??'
union all select 'shan',N'?X'
union all select 'shang',N'?y'
union all select 'shao',N'潲'
union all select 'she',N'??'
union all select 'shen',N'??'
union all select 'sheng',N'?'
union all select 'shi',N'??' --?|?a?经?
union all select 'shou',N'?'
union all select 'shu',N'?'
union all select 'shua',N'?X'
union all select 'shuai',N'?i'
union all select 'shuan',N'?Y'
union all select 'shuang',N'??'
union all select 'shui',N'睡'
union all select 'shun',N'?B'
union all select 'shuo',N'?p'
union all select 'si',N'?r' --?[?A?r
union all select 'song',N'?'
union all select 'sou',N'??'
union all select 'su',N'??'
union all select 'suan',N'算'
union all select 'sui',N'?'
union all select 'sun',N'??'
union all select 'suo',N'?R'
union all select 'ta',N'?k' --?c?k
union all select 'tai',N'?M'
union all select 'tan',N'?y'
union all select 'tang',N'?C'
union all select 'tao',N'?z' --??z
union all select 'te',N'?c'
union all select 'teng',N'?Y' --?L?z?Y
union all select 'ti',N'?'
union all select 'tian',N'?q'
union all select 'tiao',N'?g'
union all select 'tie',N'餮'
union all select 'ting',N'?h' --??h
union all select 'tong',N'?q'
union all select 'tou',N'透'
union all select 'tu',N'?r'
union all select 'tuan',N'?'
union all select 'tui',N'?D'
union all select 'tun',N'?d'
union all select 'tuo',N'?X'
union all select 'wa',N'?'
union all select 'wai',N'?'
union all select 'wan',N'?@'
union all select 'wang',N'?R'
union all select 'wei',N'?^'
union all select 'wen',N'璺'
union all select 'weng',N'?N'
union all select 'wo',N'?}'
union all select 'wu',N'?F'
union all select 'xi',N'?a'
union all select 'xia',N'?]'
union all select 'xian',N'?E'
union all select 'xiang',N'?P'
union all select 'xiao',N'??'
union all select 'xie',N'躞'
union all select 'xin',N'?'
union all select 'xing',N'?B'
union all select 'xiong',N'??'
union all select 'xiu',N'?M'
union all select 'xu',N'蓿'
union all select 'xuan',N'?K'
union all select 'xue',N'?y'
union all select 'xun',N'?R'
union all select 'ya',N'??'
union all select 'yan',N'??'
union all select 'yang',N'??'
union all select 'yao',N'?'
union all select 'ye',N'??' --?E???
union all select 'yi',N'?~'
union all select 'yin',N'??'
union all select 'ying',N'?G'
union all select 'yo',N'??'
union all select 'yong',N'?k'
union all select 'you',N'鼬'
union all select 'yu',N'??'
union all select 'yuan',N'?'
union all select 'yue',N'?V'
union all select 'yun',N'?'
union all select 'za',N'?{'
union all select 'zai',N'?f'
union all select 'zan',N'?'
union all select 'zang',N'?K'
union all select 'zao',N'?^'
union all select 'ze',N'?'
union all select 'zei',N'?e'
union all select 'zen',N'??'
union all select 'zeng',N'?'
union all select 'zha',N'?m'
union all select 'zhai',N'瘵'
union all select 'zhan',N'?'
union all select 'zhang',N'?d'
union all select 'zhao',N'?^'
union all select 'zhe',N'?p'
union all select 'zhen',N'?l'
union all select 'zheng',N'?C'
union all select 'zhi',N'?U'
union all select 'zhong',N'?A'
union all select 'zhou',N'?E'
union all select 'zhu',N'?T'
union all select 'zhua',N'爪'
union all select 'zhuai',N'?J'
union all select 'zhuan',N'?M'
union all select 'zhuang',N'??'
union all select 'zhui',N'?V'
union all select 'zhun',N'?'
union all select 'zhuo',N'?m'
union all select 'zi',N'?n' --?n??
union all select 'zong',N'?v'
union all select 'zou',N'??'
union all select 'zu',N'?'
union all select 'zuan',N'攥'
union all select 'zui',N'??'
union all select 'zun',N'?'
union all select 'zuo',N'??') t
where word >= @word collate Chinese_PRC_CS_AS_KS_WS
order by word ASC) else @word end)
set @i = @i + 1
end
return @pinyin
END
GO

SELECT dbo.fn_GetPinyin('中华人民共和国')

/*

-------------------------------------------
zuo zhong hua ren min gong he guo

(1 行受影响)
*/