--方法一sqlserver汉字转拼音首字母--调用方法 select dbo.procGetPY ('中國')Create FUNCTION dbo.procGetPY
(
@str NVARCHAR(4000)
)
/*
select dbo. procGetPYFirstLetter ('中國')
*/
RETURNS NVARCHAR(4000)
--WITH ENCRYPTION
ASBEGINDECLARE @WORD NCHAR(1),@PY NVARCHAR(4000)
SET @PY=''
WHILE LEN(@STR)>0BEGINSET @WORD=LEFT(@STR,1)
--如果非漢字字符﹐返回原字符
SET @PY=@PY+(CASEWHEN UNICODE(@WORD) BETWEEN 19968AND19968+20901THEN (
SELECT TOP 1 PY
FROM
(
SELECT'A'AS PY,N'驁'AS WORD
UNIONALLSELECT'B',N'簿'UNIONALLSELECT'C',N'錯'UNIONALLSELECT'D',N'鵽'UNIONALLSELECT'E',N'樲'UNIONALLSELECT'F',N'鰒'UNIONALLSELECT'G',N'腂'UNIONALLSELECT'H',N'夻'UNIONALLSELECT'J',N'攈'UNIONALLSELECT'K',N'穒'UNIONALLSELECT'L',N'鱳'UNIONALLSELECT'M',N'旀'UNIONALLSELECT'N',N'桛'UNIONALLSELECT'O',N'漚'UNIONALLSELECT'P',N'曝'UNIONALLSELECT'Q',N'囕'UNIONALLSELECT'R',N'鶸'UNIONALLSELECT'S',N'蜶'UNIONALLSELECT'T',N'籜'UNIONALLSELECT'W',N'鶩'UNIONALLSELECT'X',N'鑂'UNIONALLSELECT'Y',N'韻'UNIONALLSELECT'Z',N'做'
) T
WHERE WORD>=@WORD COLLATE CHINESE_PRC_CS_AS_KS_WS
ORDERBY PY ASC
)
ELSE @WORD
END)
SET @STR=RIGHT(@STR,LEN(@STR)-1)
END
RETURN @PY
ENDGo
--方法二sqlserver汉字转全拼
--调用方法 select dbo. procGetPinYin ('中國')
create function [dbo].procGetPinYin(@str varchar(100))
returns varchar(8000)
as
begin
declare @re varchar(8000),@crs varchar(10)
declare @strlen int
select @strlen=len(@str),@re=''while@strlen>0begin
set @crs= substring(@str,@strlen,1)
select @re=
casewhen@crs<'吖'then@crswhen@crs<='厑'then'a'when@crs<='靉'then'ai'when@crs<='黯'then'an'when@crs<='醠'then'ang'when@crs<='驁'then'ao'when@crs<='欛'then'ba'when@crs<='瓸'then'bai'when@crs<='瓣'then'ban'when@crs<='鎊'then'bang'when@crs<='鑤'then'bao'when@crs<='鐾'then'bei'when@crs<='輽'then'ben'when@crs<='鏰'then'beng'when@crs<='鼊'then'bi'when@crs<='變'then'bian'when@crs<='鰾'then'biao'when@crs<='彆'then'bie'when@crs<='鬢'then'bin'when@crs<='靐'then'bing'when@crs<='蔔'then'bo'when@crs<='簿'then'bu'when@crs<='囃'then'ca'when@crs<='乲'then'cai'when@crs<='爘'then'can'when@crs<='賶'then'cang'when@crs<='鼜'then'cao'when@crs<='簎'then'ce'when@crs<='笒'then'cen'when@crs<='乽'then'ceng'when@crs<='詫'then'cha'when@crs<='囆'then'chai'when@crs<='顫'then'chan'when@crs<='韔'then'chang'when@crs<='觘'then'chao'when@crs<='爡'then'che'when@crs<='讖'then'chen'when@crs<='秤'then'cheng'when@crs<='鷘'then'chi'when@crs<='銃'then'chong'when@crs<='殠'then'chou'when@crs<='矗'then'chu'when@crs<='踹'then'chuai'when@crs<='鶨'then'chuan'when@crs<='愴'then'chuang'when@crs<='顀'then'chui'when@crs<='蠢'then'chun'when@crs<='縒'then'chuo'when@crs<='嗭'then'ci'when@crs<='謥'then'cong'when@crs<='輳'then'cou'when@crs<='顣'then'cu'when@crs<='爨'then'cuan'when@crs<='臎'then'cui'when@crs<='籿'then'cun'when@crs<='錯'then'cuo'when@crs<='橽'then'da'when@crs<='靆'then'dai'when@crs<='饏'then'dan'when@crs<='闣'then'dang'when@crs<='纛'then'dao'when@crs<='的'then'de'when@crs<='扽'then'den'when@crs<='鐙'then'deng'when@crs<='螮'then'di'when@crs<='嗲'then'dia'when@crs<='驔'then'dian'when@crs<='鑃'then'diao'when@crs<='嚸'then'die'when@crs<='顁'then'ding'when@crs<='銩'then'diu'when@crs<='霘'then'dong'when@crs<='鬭'then'dou'when@crs<='蠹'then'du'when@crs<='叾'then'duan'when@crs<='譵'then'dui'when@crs<='踲'then'dun'when@crs<='鵽'then'duo'when@crs<='鱷'then'e'when@crs<='摁'then'en'when@crs<='鞥'then'eng'when@crs<='樲'then'er'when@crs<='髮'then'fa'when@crs<='瀪'then'fan'when@crs<='放'then'fang'when@crs<='靅'then'fei'when@crs<='鱝'then'fen'when@crs<='覅'then'feng'when@crs<='梻'then'fo'when@crs<='鴀'then'fou'when@crs<='猤'then'fu'when@crs<='魀'then'ga'when@crs<='瓂'then'gai'when@crs<='灨'then'gan'when@crs<='戇'then'gang'when@crs<='鋯'then'gao'when@crs<='獦'then'ge'when@crs<='給'then'gei'when@crs<='搄'then'gen'when@crs<='堩'then'geng'when@crs<='兣'then'gong'when@crs<='購'then'gou'when@crs<='顧'then'gu'when@crs<='詿'then'gua'when@crs<='恠'then'guai'when@crs<='鱹'then'guan'when@crs<='撗'then'guang'when@crs<='鱥'then'gui'when@crs<='謴'then'gun'when@crs<='腂'then'guo'when@crs<='哈'then'ha'when@crs<='饚'then'hai'when@crs<='鶾'then'han'when@crs<='沆'then'hang'when@crs<='兞'then'hao'when@crs<='靏'then'he'when@crs<='嬒'then'hei'when@crs<='恨'then'hen'when@crs<='堼'then'heng'when@crs<='鬨'then'hong'when@crs<='鱟'then'hou'when@crs<='鸌'then'hu'when@crs<='蘳'then'hua'when@crs<='蘾'then'huai'when@crs<='鰀'then'huan'when@crs<='鎤'then'huang'when@crs<='顪'then'hui'when@crs<='諢'then'hun'when@crs<='夻'then'huo'when@crs<='驥'then'ji'when@crs<='嗧'then'jia'when@crs<='鑳'then'jian'when@crs<='謽'then'jiang'when@crs<='釂'then'jiao'when@crs<='繲'then'jie'when@crs<='齽'then'jin'when@crs<='竸'then'jing'when@crs<='蘔'then'jiong'when@crs<='欍'then'jiu'when@crs<='爠'then'ju'when@crs<='羂'then'juan'when@crs<='钁'then'jue'when@crs<='攈'then'jun'when@crs<='鉲'then'ka'when@crs<='乫'then'kai'when@crs<='矙'then'kan'when@crs<='閌'then'kang'when@crs<='鯌'then'kao'when@crs<='騍'then'ke'when@crs<='褃'then'ken'when@crs<='鏗'then'keng'when@crs<='廤'then'kong'when@crs<='鷇'then'kou'when@crs<='嚳'then'ku'when@crs<='骻'then'kua'when@crs<='鱠'then'kuai'when@crs<='窾'then'kuan'when@crs<='鑛'then'kuang'when@crs<='鑎'then'kui'when@crs<='睏'then'kun'when@crs<='穒'then'kuo'when@crs<='鞡'then'la'when@crs<='籟'then'lai'when@crs<='糷'then'lan'when@crs<='唥'then'lang'when@crs<='軂'then'lao'when@crs<='餎'then'le'when@crs<='脷'then'lei'when@crs<='睖'then'leng'when@crs<='瓈'then'li'when@crs<='倆'then'lia'when@crs<='纞'then'lian'when@crs<='鍄'then'liang'when@crs<='瞭'then'liao'when@crs<='鱲'then'lie'when@crs<='轥'then'lin'when@crs<='炩'then'ling'when@crs<='咯'then'liu'when@crs<='贚'then'long'when@crs<='鏤'then'lou'when@crs<='氇'then'lu'when@crs<='鑢'then'lv'when@crs<='亂'then'luan'when@crs<='擽'then'lue'when@crs<='論'then'lun'when@crs<='鱳'then'luo'when@crs<='嘛'then'ma'when@crs<='霢'then'mai'when@crs<='蘰'then'man'when@crs<='蠎'then'mang'when@crs<='唜'then'mao'when@crs<='癦'then'me'when@crs<='嚜'then'mei'when@crs<='們'then'men'when@crs<='霥'then'meng'when@crs<='羃'then'mi'when@crs<='麵'then'mian'when@crs<='廟'then'miao'when@crs<='鱴'then'mie'when@crs<='鰵'then'min'when@crs<='詺'then'ming'when@crs<='謬'then'miu'when@crs<='耱'then'mo'when@crs<='麰'then'mou'when@crs<='旀'then'mu'when@crs<='魶'then'na'when@crs<='錼'then'nai'when@crs<='婻'then'nan'when@crs<='齉'then'nang'when@crs<='臑'then'nao'when@crs<='呢'then'ne'when@crs<='焾'then'nei'when@crs<='嫩'then'nen'when@crs<='能'then'neng'when@crs<='嬺'then'ni'when@crs<='艌'then'nian'when@crs<='釀'then'niang'when@crs<='脲'then'niao'when@crs<='钀'then'nie'when@crs<='拰'then'nin'when@crs<='濘'then'ning'when@crs<='靵'then'niu'when@crs<='齈'then'nong'when@crs<='譳'then'nou'when@crs<='搙'then'nu'when@crs<='衄'then'nv'when@crs<='瘧'then'nue'when@crs<='燶'then'nuan'when@crs<='桛'then'nuo'when@crs<='鞰'then'o'when@crs<='漚'then'ou'when@crs<='袙'then'pa'when@crs<='磗'then'pai'when@crs<='鑻'then'pan'when@crs<='胖'then'pang'when@crs<='礮'then'pao'when@crs<='轡'then'pei'when@crs<='喯'then'pen'when@crs<='喸'then'peng'when@crs<='鸊'then'pi'when@crs<='騙'then'pian'when@crs<='慓'then'piao'when@crs<='嫳'then'pie'when@crs<='聘'then'pin'when@crs<='蘋'then'ping'when@crs<='魄'then'po'when@crs<='哛'then'pou'when@crs<='曝'then'pu'when@crs<='蟿'then'qi'when@crs<='髂'then'qia'when@crs<='縴'then'qian'when@crs<='瓩'then'qiang'when@crs<='躈'then'qiao'when@crs<='籡'then'qie'when@crs<='藽'then'qin'when@crs<='櫦'then'qing'when@crs<='瓗'then'qiong'when@crs<='糗'then'qiu'when@crs<='覻'then'qu'when@crs<='勸'then'quan'when@crs<='礭'then'que'when@crs<='囕'then'qun'when@crs<='橪'then'ran'when@crs<='讓'then'rang'when@crs<='繞'then'rao'when@crs<='熱'then're'when@crs<='餁'then'ren'when@crs<='陾'then'reng'when@crs<='馹'then'ri'when@crs<='穃'then'rong'when@crs<='嶿'then'rou'when@crs<='擩'then'ru'when@crs<='礝'then'ruan'when@crs<='壡'then'rui'when@crs<='橍'then'run'when@crs<='鶸'then'ruo'when@crs<='栍'then'sa'when@crs<='虄'then'sai'when@crs<='閐'then'san'when@crs<='喪'then'sang'when@crs<='髞'then'sao'when@crs<='飋'then'se'when@crs<='篸'then'sen'when@crs<='縇'then'seng'when@crs<='霎'then'sha'when@crs<='曬'then'shai'when@crs<='鱔'then'shan'when@crs<='緔'then'shang'when@crs<='潲'then'shao'when@crs<='欇'then'she'when@crs<='瘮'then'shen'when@crs<='賸'then'sheng'when@crs<='瓧'then'shi'when@crs<='鏉'then'shou'when@crs<='虪'then'shu'when@crs<='誜'then'shua'when@crs<='卛'then'shuai'when@crs<='腨'then'shuan'when@crs<='灀'then'shuang'when@crs<='睡'then'shui'when@crs<='鬊'then'shun'when@crs<='鑠'then'shuo'when@crs<='乺'then'si'when@crs<='鎹'then'song'when@crs<='瘶'then'sou'when@crs<='鷫'then'su'when@crs<='算'then'suan'when@crs<='鐩'then'sui'when@crs<='潠'then'sun'when@crs<='蜶'then'suo'when@crs<='襨'then'ta'when@crs<='燤'then'tai'when@crs<='賧'then'tan'when@crs<='燙'then'tang'when@crs<='畓'then'tao'when@crs<='蟘'then'te'when@crs<='朰'then'teng'when@crs<='趯'then'ti'when@crs<='舚'then'tian'when@crs<='糶'then'tiao'when@crs<='餮'then'tie'when@crs<='乭'then'ting'when@crs<='憅'then'tong'when@crs<='透'then'tou'when@crs<='鵵'then'tu'when@crs<='褖'then'tuan'when@crs<='駾'then'tui'when@crs<='坉'then'tun'when@crs<='籜'then'tuo'when@crs<='韤'then'wa'when@crs<='顡'then'wai'when@crs<='贎'then'wan'when@crs<='朢'then'wang'when@crs<='躛'then'wei'when@crs<='璺'then'wen'when@crs<='齆'then'weng'when@crs<='齷'then'wo'when@crs<='鶩'then'wu'when@crs<='衋'then'xi'when@crs<='鏬'then'xia'when@crs<='鼸'then'xian'when@crs<='鱌'then'xiang'when@crs<='斆'then'xiao'when@crs<='躞'then'xie'when@crs<='釁'then'xin'when@crs<='臖'then'xing'when@crs<='敻'then'xiong'when@crs<='齅'then'xiu'when@crs<='蓿'then'xu'when@crs<='贙'then'xuan'when@crs<='瀥'then'xue'when@crs<='鑂'then'xun'when@crs<='齾'then'ya'when@crs<='灩'then'yan'when@crs<='樣'then'yang'when@crs<='鑰'then'yao'when@crs<='岃'then'ye'when@crs<='齸'then'yi'when@crs<='檼'then'yin'when@crs<='譍'then'ying'when@crs<='喲'then'yo'when@crs<='醟'then'yong'when@crs<='鼬'then'you'when@crs<='爩'then'yu'when@crs<='願'then'yuan'when@crs<='鸙'then'yue'when@crs<='韻'then'yun'when@crs<='雥'then'za'when@crs<='縡'then'zai'when@crs<='饡'then'zan'when@crs<='臟'then'zang'when@crs<='竈'then'zao'when@crs<='稄'then'ze'when@crs<='鱡'then'zei'when@crs<='囎'then'zen'when@crs<='贈'then'zeng'when@crs<='醡'then'zha'when@crs<='瘵'then'zhai'when@crs<='驏'then'zhan'when@crs<='瞕'then'zhang'when@crs<='羄'then'zhao'when@crs<='鷓'then'zhe'when@crs<='黮'then'zhen'when@crs<='證'then'zheng'when@crs<='豒'then'zhi'when@crs<='諥'then'zhong'when@crs<='驟'then'zhou'when@crs<='鑄'then'zhu'when@crs<='爪'then'zhua'when@crs<='跩'then'zhuai'when@crs<='籑'then'zhuan'when@crs<='戅'then'zhuang'when@crs<='鑆'then'zhui'when@crs<='稕'then'zhun'when@crs<='籱'then'zhuo'when@crs<='漬'then'zi'when@crs<='縱'then'zong'when@crs<='媰'then'zou'when@crs<='謯'then'zu'when@crs<='攥'then'zuan'when@crs<='欈'then'zui'when@crs<='銌'then'zun'when@crs<='咗'then'zuo'
--else@crsend+' '+@re,@strlen=@strlen-1
--去掉拼音之间的间隔
else@crsend+''+@re,@strlen=@strlen-1endreturn(@re)
end
go