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

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

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

SELECTdbo.fn_GetPinyin("中华人民共和国")

/*

-------------------------------------------
zuozhonghuarenmingongheguo

(1行受影响)
*/