在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]   
go
create function f_GetPy(@Str nvarchar(400))
returns nvarchar(4000)
as
begin
declare @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>0
begin
select top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr<=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
go
/*
--测试
select dbo.f_GetPy('广州市') as 广州市,dbo.f_GetPy('广东人') as 广东人

--以后查询的时候,就可以调用上面的函数来实现汉字模糊查询

select * from dbo.vw_OilList where dbo.f_getpy(oilname)='93#qy'
*/