--============================================
--创建辅助表IF(OBJECT_ID('dbo.PinYin','U') IS NOT NULL)BEGINDROP TABLE dbo.PinYinENDCREATE TABLE dbo.PinYin( Word NVARCHAR(200) PRIMARY KEY, PY NVARCHAR(200))--============================================--插入辅助数据INSERT INTO dbo.PinYin(PY,Word)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'咗'GO--============================================--创建函数，循环遍历字符串，使用辅助表来获取遍历得到的字符的拼音，IF(OBJECT_ID('dbo.ufn_GetPy','FN') IS NOT NULL)BEGINDROP FUNCTION dbo.ufn_GetPy ENDGOCREATE FUNCTION dbo.ufn_GetPy(@Str NVARCHAR(500)='')RETURNS NVARCHAR(500)ASBEGIN DECLARE @strlen INT; SELECT @strlen=LEN(@str); DECLARE @i INT; DECLARE @result NVARCHAR(500); SET @result=N'' SET @i=0; --空字符串返回空 IF(@strlen<1) BEGIN RETURN ''; END WHILE(@i<@strlen) BEGIN SET @i=@i+1 DECLARE @tempChar NVARCHAR(50); DECLARE @tempPY NVARCHAR(50); SET @tempChar=substring(@str,@i,1); IF(@tempChar>N'z') BEGIN SELECT TOP(1) @tempPY=T.py FROM dbo.PinYin T WHERE T.word>=@tempChar ORDER BY T.word COLLATE Chinese_PRC_CI_AS; SET @result=@result+@tempPY+' '; END END RETURN @resultENDGO--==========================================================--测试SELECT dbo.ufn_GetPy(N'中华人民共和国')