资源描述：
《通过SQLServer取出汉字的拼音.doc》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、SQL函数实现用拼音对汉字模糊查询方法一：createfunctionf_GetPy(@Strnvarchar(400))returnsnvarchar(4000)asbegindeclare@strlenint,@renvarchar(4000)declare@ttable(chrnchar(1)collateChinese_PRC_CI_AS,letternchar(1))insert@tselect'吖','A'unionallselect'八','B'unionallselect'嚓','C'unionallselect'咑','D'unionallselect'妸','E'uni
2、onallselect'发','F'unionallselect'旮','G'unionallselect'铪','H'unionallselect'丌','J'unionallselect'咔','K'unionallselect'垃','L'unionallselect'嘸','M'unionallselect'拏','N'unionallselect'噢','O'unionallselect'妑','P'unionallselect'七','Q'unionallselect'呥','R'unionallselect'仨','S'unionallselect'他','T'unional
3、lselect'屲','W'unionallselect'夕','X'unionallselect'丫','Y'unionallselect'帀','Z'select@strlen=len(@str),@re=''while@strlen>0beginselecttop1@re=letter+@re,@strlen=@strlen-1from@tawherechr<=substring(@str,@strlen,1)orderbychrdescif@@rowcount=0select@re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1end
4、return(@re)endgo--测试selectdbo.f_GetPy('东莞市')as东莞市,dbo.f_GetPy('ab中c国人')as中国人--以后查询的时候,就可以调用上面的函数来实现汉字模糊查询select*from表wheredbo.f_getpy(字段)='zgyh'方法二：CREATEFUNCTIONf_GetPY(@strnvarchar(4000))RETURNSnvarchar(4000)ASBEGINDECLARE@pyTABLE(chchar(1),hz1nchar(1)COLLATEChinese_PRC_CS_AS_KS_WS,hz2nchar(1)CO
5、LLATEChinese_PRC_CS_AS_KS_WS)INSERT@pySELECT'A',N'吖',N'鏊'UNIONALLSELECT'B',N'八',N'簿'UNIONALLSELECT'C',N'嚓',N'错'UNIONALLSELECT'D',N'哒',N'跺'UNIONALLSELECT'E',N'屙',N'贰'UNIONALLSELECT'F',N'发',N'馥'UNIONALLSELECT'G',N'旮',N'过'UNIONALLSELECT'H',N'铪',N'蠖'UNIONALLSELECT'J',N'丌',N'竣'UNIONALLSELECT'K',N'咔',N'
6、廓'UNIONALLSELECT'L',N'垃',N'雒'UNIONALLSELECT'M',N'妈',N'穆'UNIONALLSELECT'N',N'拿',N'糯'UNIONALLSELECT'O',N'噢',N'沤'UNIONALLSELECT'P',N'趴',N'曝'UNIONALLSELECT'Q',N'七',N'群'UNIONALLSELECT'R',N'蚺',N'箬'UNIONALLSELECT'S',N'仨',N'锁'UNIONALLSELECT'T',N'他',N'箨'UNIONALLSELECT'W',N'哇',N'鋈'UNIONALLSELECT'X',N'夕',N'蕈
7、'UNIONALLSELECT'Y',N'丫',N'蕴'UNIONALLSELECT'Z',N'匝',N'做'DECLARE@iintSET@i=PATINDEX('%[吖-做]%'COLLATEChinese_PRC_CS_AS_KS_WS,@str)WHILE@i>0SELECT@str=REPLACE(@str,SUBSTRING(@str,@i,1),ch),@i=PATINDEX('%[吖-做]%'COLLAT
