取汉字首字母的两个函数

最新推荐文章于 2024-03-19 19:18:00 发布

转载最新推荐文章于 2024-03-19 19:18:00 发布 · 121 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/accumulater/p/6244467.html

本文介绍两个SQL Server函数，用于从汉字中提取每个字的拼音首字母，并提供示例展示函数效果。但注意到这两种方法均未能解决多音字问题。

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

create function [dbo].[f_getpy_V1] (@str nvarchar(4000))

returns nvarchar(4000)

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

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

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

returns varchar(500)

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

V21

--------

WLG

V21

--------

V22

--------

--由上我们可以看到，两种方法都没有解决多音字的问题。

转载于:https://www.cnblogs.com/accumulater/p/6244467.html