复制代码 代码如下:
/* -------------------------------------------------------------
函数: 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' '
union all select 'an',N'黯'
union all select 'ang',N' '
union all select 'ao',N' '
union all select 'ba',N' '
union all select 'bai',N' ' --
union all select 'ban',N'瓣'
union all select 'bang',N' '
union all select 'bao',N' '
union all select 'bei',N'鐾'
union all select 'ben',N' '
union all select 'beng',N' '
union all select 'bi',N' '
union all select 'bian',N' '
union all select 'biao',N' '
union all select 'bie',N' '
union all select 'bin',N' '
union all select 'bing',N' '
union all select 'bo',N' '
union all select 'bu',N'簿'
union all select 'ca',N' '
union all select 'cai',N' ' --
union all select 'can',N' '
union all select 'cang',N' '
union all select 'cao',N' '
union all select 'ce',N' '
union all select 'cen',N' '
union all select 'ceng',N' ' --
union all select 'cha',N' '
union all select 'chai',N' '
union all select 'chan',N' '
union all select 'chang',N' '
union all select 'chao',N' '
union all select 'che',N' '
union all select 'chen',N' '
union all select 'cheng',N'秤'
union all select 'chi',N' '
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' '
union all select 'chuang',N' '
union all select 'chui',N' '
union all select 'chun',N'蠢'
union all select 'chuo',N' '
union all select 'ci',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' '
union all select 'cuo',N' '
union all select 'da',N' '
union all select 'dai',N' '
union all select 'dan',N' '
union all select 'dang',N' '
union all select 'dao',N'纛'
union all select 'de',N'的'
union all select 'den',N' '
union all select 'deng',N' '
union all select 'di',N' '
union all select 'dia',N'嗲'
union all select 'dian',N' '
union all select 'diao',N' '
union all select 'die',N' ' --
union all select 'ding',N' '
union all select 'diu',N' '
union all select 'dong',N' '
union all select 'dou',N' '
union all select 'du',N'蠹'
union all select 'duan',N' ' --
union all select 'dui',N' '
union all select 'dun',N' '
union all select 'duo',N' '
union all select 'e',N' '
union all select 'en',N'摁'
union all select 'eng',N' '
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' '
union all select 'feng',N' '
union all select 'fo',N' '
union all select 'fou',N' '
union all select 'fu',N' ' --
union all select 'ga',N' '
union all select 'gai',N' '
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' '
union all select 'gen',N' '
union all select 'geng',N' ' --
union all select 'gong',N' ' --
union all select 'gou',N' '
union all select 'gu',N' '
union all select 'gua',N' '
union all select 'guai',N' '
union all select 'guan',N' '
union all select 'guang',N' '
union all select 'gui',N' '
union all select 'gun',N' '
union all select 'guo',N' '
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' '
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' '
union all select 'hu',N' '
union all select 'hua',N' '
union all select 'huai',N' '
union all select 'huan',N' '
union all select 'huang',N' '
union all select 'hui',N' '
union all select 'hun',N' '
union all select 'huo',N' '
union all select 'ji',N' '
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' '
union all select 'jin',N' '
union all select 'jing',N' '
union all select 'jiong',N' '
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' '
union all select 'ka',N' '
union all select 'kai',N' ' --
union all select 'kan',N' '
union all select 'kang',N' '
union all select 'kao',N' '
union all select 'ke',N' '
union all select 'ken',N' '
union all select 'keng',N' ' --
union all select 'kong',N' '
union all select 'kou',N' '
union all select 'ku',N' '
union all select 'kua',N' '
union all select 'kuai',N' '
union all select 'kuan',N' '
union all select 'kuang',N' '
union all select 'kui',N' '
union all select 'kun',N' '
union all select 'kuo',N' '
union all select 'la',N' '
union all select 'lai',N' '
union all select 'lan',N' '
union all select 'lang',N' '
union all select 'lao',N' '
union all select 'le',N' '
union all select 'lei',N' ' --嘞
union all select 'leng',N' '
union all select 'li',N' '
union all select 'lia',N' '
union all select 'lian',N' '
union all select 'liang',N' '
union all select 'liao',N' '
union all select 'lie',N' '
union all select 'lin',N' ' -- 拎
union all select 'ling',N' '
union all select 'liu',N'咯' -- 咯
union all select 'long',N' '
union all select 'lou',N' '
union all select 'lu',N'氇'
union all select 'lv',N' '
union all select 'luan',N' '
union all select 'lue',N' '
union all select 'lun',N' '
union all select 'luo',N' '
union all select 'ma',N'嘛'
union all select 'mai',N' '
union all select 'man',N' '
union all select 'mang',N' '
union all select 'mao',N' '
union all select 'me',N' ' --
union all select 'mei',N' '
union all select 'men',N' '
union all select 'meng',N' ' --
union all select 'mi',N' '
union all select 'mian',N' '
union all select 'miao',N' '
union all select 'mie',N' ' --
union all select 'min',N' '
union all select 'ming',N' '
union all select 'miu',N' '
union all select 'mo',N'耱' --耱
union all select 'mou',N' ' --
union all select 'mu',N' '
union all select 'na',N' '
union all select 'nai',N' '
union all select 'nan',N' '
union all select 'nang',N' '
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'能' -- 嗯
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' '
union all select 'nou',N' '
union all select 'nu',N' '
union all select 'nv',N'衄'
union all select 'nue',N' '
union all select 'nuan',N' ' --
union all select 'nuo',N' '
union all select 'o',N' ' -- î
union all select 'ou',N' '
union all select 'pa',N' '
union all select 'pai',N' ' --
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' ' --
union all select 'pi',N' '
union all select 'pian',N' '
union all select 'piao',N' '
union all select 'pie',N' '
union all select 'pin',N'聘'
union all select 'ping',N' '
union all select 'po',N'魄'
union all select 'pou',N' ' --
union all select 'pu',N'曝'
union all select 'qi',N' '
union all select 'qia',N'髂'
union all select 'qian',N' '
union all select 'qiang',N' ' --
union all select 'qiao',N' '
union all select 'qie',N' '
union all select 'qin',N' '
union all select 'qing',N' '
union all select 'qiong',N' '
union all select 'qiu',N'糗'
union all select 'qu',N' '
union all select 'quan',N' '
union all select 'que',N' '
union all select 'qun',N' '
union all select 'ran',N' '
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' '
union all select 'ruan',N' '
union all select 'rui',N' '
union all select 'run',N' ' --
union all select 'ruo',N' '
union all select 'sa',N' ' --
union all select 'sai',N' ' --
union all select 'san',N' '
union all select 'sang',N' '
union all select 'sao',N' '
union all select 'se',N' ' --
union all select 'sen',N' '
union all select 'seng',N' ' --
union all select 'sha',N'霎'
union all select 'shai',N' '
union all select 'shan',N' '
union all select 'shang',N' '
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' ' --
union all select 'shou',N' '
union all select 'shu',N' '
union all select 'shua',N' '
union all select 'shuai',N' '
union all select 'shuan',N' '
union all select 'shuang',N' '
union all select 'shui',N'睡'
union all select 'shun',N' '
union all select 'shuo',N' '
union all select 'si',N' ' --
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' '
union all select 'ta',N' ' --
union all select 'tai',N' '
union all select 'tan',N' '
union all select 'tang',N' '
union all select 'tao',N' ' --
union all select 'te',N' '
union all select 'teng',N' ' --
union all select 'ti',N' '
union all select 'tian',N' '
union all select 'tiao',N' '
union all select 'tie',N'餮'
union all select 'ting',N' ' --
union all select 'tong',N' '
union all select 'tou',N'透'
union all select 'tu',N' '
union all select 'tuan',N' '
union all select 'tui',N' '
union all select 'tun',N' '
union all select 'tuo',N' '
union all select 'wa',N' '
union all select 'wai',N' '
union all select 'wan',N' '
union all select 'wang',N' '
union all select 'wei',N' '
union all select 'wen',N'璺'
union all select 'weng',N' '
union all select 'wo',N' '
union all select 'wu',N' '
union all select 'xi',N' '
union all select 'xia',N' '
union all select 'xian',N' '
union all select 'xiang',N' '
union all select 'xiao',N' '
union all select 'xie',N'躞'
union all select 'xin',N' '
union all select 'xing',N' '
union all select 'xiong',N' '
union all select 'xiu',N' '
union all select 'xu',N'蓿'
union all select 'xuan',N' '
union all select 'xue',N' '
union all select 'xun',N' '
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' ' --
union all select 'yi',N' '
union all select 'yin',N' '
union all select 'ying',N' '
union all select 'yo',N' '
union all select 'yong',N' '
union all select 'you',N'鼬'
union all select 'yu',N' '
union all select 'yuan',N' '
union all select 'yue',N' '
union all select 'yun',N' '
union all select 'za',N' '
union all select 'zai',N' '
union all select 'zan',N' '
union all select 'zang',N' '
union all select 'zao',N' '
union all select 'ze',N' '
union all select 'zei',N' '
union all select 'zen',N' '
union all select 'zeng',N' '
union all select 'zha',N' '
union all select 'zhai',N'瘵'
union all select 'zhan',N' '
union all select 'zhang',N' '
union all select 'zhao',N' '
union all select 'zhe',N' '
union all select 'zhen',N' '
union all select 'zheng',N' '
union all select 'zhi',N' '
union all select 'zhong',N' '
union all select 'zhou',N' '
union all select 'zhu',N' '
union all select 'zhua',N'爪'
union all select 'zhuai',N' '
union all select 'zhuan',N' '
union all select 'zhuang',N' '
union all select 'zhui',N' '
union all select 'zhun',N' '
union all select 'zhuo',N' '
union all select 'zi',N' ' --
union all select 'zong',N' '
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 行受影响)
*/
