sqlServer 获取汉字字串的拼音声母

复制代码 代码如下:

create function fgetpy(@str varchar(500)=”)

returns varchar(500)

as

begin

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

declare @c nchar(1),@chn nchar(1)

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

set @ii=0

while @ii<@strlen

begin

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

if @chn>=’吖’

select @c = char(count(*)+63) 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

where b.chn <=@chn

else set @c=@chn

set @return=@return+@c

end

return(@return)

end