Sql server 如何得到汉字的声母

在sql中创建用户自定义拼音函数：

create function f_GetPy(@Str nvarchar(400))

returns nvarchar(4000)

as

begin

declare @strlen int,@re nvarchar(4000)

declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))

insert @t select '吖','A' union all select '八','B'

union all select '嚓','C' union all select '咑','D'

union all select '妸','E' union all select '发','F'

union all select '旮','G' union all select '铪','H'

union all select '丌','J' union all select '咔','K'

union all select '垃','L' union all select '嘸','M'

union all select '拏','N' union all select '噢','O'

union all select '妑','P' union all select '七','Q'

union all select '呥','R' union all select '仨','S'

union all select '他','T' union all select '屲','W'

union all select '夕','X' union all select '丫','Y'

union all select '帀','Z'

select @strlen=len(@str),@re=''

while @strlen>0

begin

select top 1 @re=letter+@re,@strlen=@strlen-1

from @t a where chr<=substring(@str,@strlen,1)

order by chr desc

if @@rowcount=0

select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1

end

return(@re)

end

go

--测试

select dbo.f_GetPy('东莞市') as 东莞市,dbo.f_GetPy('ab中c国人') as 中国人

--以后查询的时候,就可以调用上面的函数来实现汉字模糊查询

select * from 表 where dbo.f_getpy(字段)='zgyh'