根據(jù)漢字聲母，要在表中找相關(guān)記錄，怎么實現(xiàn)？比如輸入"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
--創(chuàng)建取拼音函數(shù)
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ā)','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 @[email protected],@[email protected]
from @t a where chr<=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)[email protected],@[email protected]
end
return(@re)
end
go
--測試
select dbo.f_getpy('東莞市') as 東莞市,dbo.f_getpy('ab中c國人') as 中國人
--以后查詢的時候,就可以調(diào)用上面的函數(shù)來實現(xiàn)漢字模糊查詢
select * from 表 where dbo.f_getpy(字段)='zgyh'
