在SQL中创建用户自定义拼音函数

--在SQL中创建用户自定义拼音函数：if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetPy]') and xtype in (N'FN', N'IF', N'TF'))   drop function [dbo].[f_GetPy]   gocreate 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 '八','B'union all select '嚓','C' union all select '咑','D'union all select '妸','E' union all select '发','F'union all select '旮','G' union all select '铪','H'union all select '丌','J' union all select '咔','K' union all select '垃','L' union all select '嘸','M'union all select '拏','N' union all select '噢','O'union all select '妑','P' union all select '七','Q'union all select '呥','R' union all select '仨','S'union all select '他','T' union all select '屲','W'union all select '夕','X' union all select '丫','Y'union all select '帀','Z'select @strlen=len(@str),@re=''while @strlen>0beginselect 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('广东人') as 广东人--以后查询的时候,就可以调用上面的函数来实现汉字模糊查询select * from dbo.vw_OilList where dbo.f_getpy(oilname)='93#qy'*/