在项目中，有时需要根据拼音来搜索对应的汉字，找了许多资料，也参考了一些示例，这个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
GO
例如：select Name, dbo.Get_QuanPin_li(Name) from tableName where (dbo.Get_QuanPin_li(Name) like‘%zhongguo%‘ or Name like‘%zhongguo%‘)
原文地址：http://blog.csdn.net/lisky119/article/details/8662673