通过SQLServer取出汉字拼音.docVIP

SQL函数实现汉字模糊查询create function f_GetPy(@Str nvarchar(400))returns nvarchar(4000)asbegindeclare @strlen int,@re nvarchar(4000)declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))insert @t select 吖,A union all select 八,Bunion all select 嚓,C union all select 咑,Dunion all select 妸,E union all select 发,Funion all select 旮,G union all select 铪,Hunion all select 丌,J union all select 咔,K union all select 垃,L union all select 嘸,Munion all select 拏,N union all select 噢,Ounion all select 妑,P union all select 七,Qunion all select 呥,R union all select 仨,Sunion all select 他,T union all select 屲,Wunion all select 夕,X union all select 丫,Yunion all select 帀,Z select @strlen=len(@str),@re=while @strlen0beginselect top 1 @re=letter+@re,@strlen=@strlen-1from @t a where chr=substring(@str,@strlen,1)order by chr descif @@rowcount=0select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1endreturn(@re)endgo--测试select dbo.f_GetPy(东莞市) as 东莞市,dbo.f_GetPy(ab中c国人) as 中国人 --以后查询的时候,就可以调用上面的函数来实现汉字模糊查询 select * from 表 where dbo.f_getpy(字段)=zgyh 方法二： CREATE FUNCTION f_GetPY(@str nvarchar(4000))RETURNS nvarchar(4000)ASBEGINDECLARE @py TABLE(ch char(1),hz1 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS,hz2 nchar(1) COLLATE Chinese_PRC_CS_AS_KS_WS)INSERT @py SELECT A,N吖,N鏊UNION ALL SELECT B,N八,N簿UNION ALL SELECT C,N嚓,N错UNION ALL SELECT D,N哒,N跺UNION ALL SELECT E,N屙,N贰UNION ALL SELECT F,N发,N馥UNION ALL SELECT G,N旮,N过UNION ALL SELECT H,N铪,N蠖UNION ALL SELECT J,N丌,N竣UNION ALL SELECT K,N咔,N廓UNION ALL SELECT L,N垃,N雒UNION ALL SELECT M,N妈,N穆UNION ALL SELECT N,N拿,N糯UNION ALL SELECT O,N噢,N沤UNION ALL SELECT P,N趴,N曝UNION ALL SELECT Q,N七,N群UNION ALL SELECT R,N蚺,N箬UNION ALL SELECT S,N仨,N锁UNION ALL SELECT T,N他,N箨UNION ALL SELECT W,N哇,N鋈UNION ALL SELECT X,N夕,N蕈UNION ALL SELECT Y,N丫,N蕴UNION ALL SELECT Z,N匝,N做 DECLARE @i intSET @i=PATINDEX(%[吖-做]% COLLATE Chinese_PRC_CS_AS_KS_WS,@str)WHILE @i0SELECT @str=REPLACE(@str,SUBSTRING(@str,@i,1),