据汉字声母，要在表中找相关记录，怎么实现

   
根据汉字声母，要在表中找相关记录，怎么实现？比如输入"zgyh"，就能找到所有“中国银行”的记录。

if exists (select * from dbo.sysobjects where id = object_id(N@#[dbo].[f_GetPy]@#) and xtype in (N@#FN@#, N@#IF@#, N@#TF@#))
drop function [dbo].[f_GetPy]
GO

--创建取拼音函数
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@#

原文转自：http://www.ltesting.net