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

根据汉字声母，要在表中找相关记录，怎么实现？比如输入"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