ifexists(select*fromdbo.sysobjectswhereid=object_id(N[dbo].[f_GetPy])andxtypein(NFN,NIF,NTF))
dropfunction[dbo].[f_GetPy]
GO
--创立取拼音函数
createfunctionf_GetPy(@Strnvarchar(400))
returnsnvarchar(4000)
as
begin
declare@strlenint,@renvarchar(4000)
declare@ttable(chrnchar(1)collateChinese_PRC_CI_AS,letternchar(1))
insert@tselect吖,Aunionallselect八,B
unionallselect嚓,Cunionallselect,D
unionallselect,Eunionallselect发,F
unionallselect旮,Gunionallselect铪,H
unionallselect丌,Junionallselect咔,K
unionallselect垃,Lunionallselect`,M
unionallselect,Nunionallselect噢,O
unionallselectr,Punionallselect七,Q
unionallselect,Runionallselect仨,S
unionallselect他,Tunionallselect,W
unionallselect夕,Xunionallselect丫,Y
unionallselect,Z
select@strlen=len(@str),@re=
while@strlen>0
begin
selecttop1@re=letter+@re,@strlen=@strlen-1
from@tawherechr<=substring(@str,@strlen,1)
orderbychrdesc
if@@rowcount=0
select@re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
go