SqlServer 汉字转换拼音首字母函数

CREATE function [dbo].[Func_GetPY](@str nvarchar(4000))
returns nvarchar(4000)
as
begin
set @str=RTRIM(@str)
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
--处理常用多音字
if len(@str)>=2
begin
if left(@str,2)='重庆'
begin
set @PY=@PY+'CQ'
if LEN(@str)>2
set @str=right(@str,len(@str)-2)
else
set @str=''
end
if left(@str,2)='*'
begin
set @PY=@PY+'XZ'
if LEN(@str)>2
set @str=right(@str,len(@str)-2)
else
set @str=''
end
if left(@str,2)='藏族'
begin
set @PY=@PY+'ZZ'
if LEN(@str)>2
set @str=right(@str,len(@str)-2)
else
set @str=''
end
end
--汉字返回拼音首字母
if 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
end
return @PY
end
GO

上一篇:day04 一个简单的代码优化案例


下一篇:MySQL中删除数据的两种方法