SQL 取汉字首字母的两个函数

go

--创建取汉字首字母函数(第一版)

create function [dbo].[f_getpy_V1] (@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 199 68+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

--创建取汉字首字母函数(第二版)

create function [dbo].[f_getpy_V2](@Str varchar(500)='') returns varchar(500)

as

begin

declare @strlen int,@return varchar(500),@ii int

declare @n int,@c char(1),@chn nchar(1)

select @strlen=len(@str),@return='',@ii=0

set @ii=0

while @ii<@strlen

begin

select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)

if @chn>'z'

select @n = @n +1

,@c =case chn when @chn then char(@n)else @c end

from(

select top 27 *from(

select chn ='吖'

union all select'八'

union all select'嚓'

union all select'咑'

union all select'妸'

union all select'发'

union all select'旮'

union all select'铪'

union all select'丌'--because have no 'i'

union all select'丌'

union all select'咔'

union all select'垃'

union all select'嘸'

union all select'拏'

union all select'噢'

union all select'妑'

union all select'七'

union all select'呥'

union all select'仨'

union all select'他'

union all select'屲'--no 'u'

union all select'屲'--no 'v'

union all select'屲'

union all select'夕'

union all select'丫'

union all select'帀'

union all select @chn)as a

order by chn COLLATE Chinese_PRC_CI_AS

)as b

else set @c='a'

set @return=@return+@c

end

return(@return)

end

--测试示例

select dbo.[f_getpy_V1]('王立国')as V11

select dbo.[f_getpy_V2]('王立国')as V21

select dbo.[f_getpy_V1]('重复')as V21

select dbo.[f_getpy_V2]('重复')as V22

--运行结果

/*

V11

--------

WLG