mssql sqlserver 获取中文字符首字母(拼音码)的方法分享


摘要:
下文记载获取中文字拼音首字母(拼音码)的方法分享,如下所示:


create function fn_getPinYin(@tmp nvarchar(10))
returns varchar(10)
as
begin
declare @tmpPinY nchar(1),@PYAll nvarchar(10)
set @PYAll=''
while len(@tmp)>0
begin
set @tmpPinY=left(@tmp,1)
--如果非汉字字符,返回原字符
set @PYAll=@PYAll+(case when unicode(@tmpPinY) between 19968 and 19968+20901
then (select top 1 PinYin from (
select 'A' as PinYin,N'驁' as tmp
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 tmp>=@tmpPinY collate Chinese_PRC_CS_AS_KS_WS
order by PinYin ASC) else @tmpPinY end)
set @tmp=right(@tmp,len(@tmp)-1)
end
return @PYAll
end
go

函数应用举例

select dbo.fn_getPinYin('猫猫小屋go365')
----输出----
MMXWgo365
mssql_sqlserver_获取拼音编码

mssql_sqlserver_获取拼音编码