sql函数生成拼音码方案

create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符，返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'A' as PY,N' ' as word
union all select 'B',N'簿'
union all select 'C',N' '
union all select 'D',N' '
union all select 'E',N' '
union all select 'F',N' '
union all select 'G',N' '
union all select 'H',N' '
union all select 'J',N' '
union all select 'K',N' '
union all select 'L',N' '
union all select 'M',N' '
union all select 'N',N' '
union all select 'O',N' '
union all select 'P',N'曝'
union all select 'Q',N' '
union all select 'R',N' '
union all select 'S',N' '
union all select 'T',N' '
union all select 'W',N' '
union all select 'X',N' '
union all select 'Y',N' '
union all select 'Z',N' '
) T
where word>





=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC) else @word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end
go
--函数调用实例：
select dbo.fun_getPY('中华人民共和国'),dbo.fun_getPY('中 人民共和 '