SQL SERVER中的汉字转拼音-实现根据拼音搜索汉字

浏览数：30 / 时间：2015年06月12日

在项目中，有时需要根据拼音来搜索对应的汉字，找了许多资料，也参考了一些示例，这个sql函数总是解决了这个问题，而且性能是找的资料中最好的。

原理是通过这个函数将中文汉字的字段中的数据转换成拼音，再根据拼音来进行搜索。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <lisky.li>
-- Create date: <2013>
-- Description: <汉字转全拼音函数>
-- =============================================
create function [dbo].[Get_QuanPin_li](@str varchar(120))
returns varchar(8000)
as
begin declare @re varchar(8000),@crs varchar(10) declare @strlen int
select @strlen=len(@str),@re=‘‘ while @strlen>0 begin    set @crs= substring(@str,@strlen,1)      select @re=        case        when @crs<‘吖‘ then @crs        when @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 @crs end+‘ ‘+@re,@strlen=@strlen-1
end return(@re)
end