oracle中fn_getpy函数,一个sql server中用到的函数，用来取拼音-数据库专栏,SQL Server...

create function f_getpy(@str nvarchar(4000))

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 into @t(chr,letter)

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 * from alls where dbo.f_getpy(name)=cyml