文章內(nèi)容

SQL Server 得到漢字聲母的方法

發(fā)布時(shí)間: 2012/8/4 16:47:57

在SQL中創(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 @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
--測(cè)試
select dbo.f_GetPy('東莞市') as 東莞市,dbo.f_GetPy('ab中c國(guó)人') as 中國(guó)人

--以后查詢的時(shí)候,就可以調(diào)用上面的函數(shù)來(lái)實(shí)現(xiàn)漢字模糊查詢

select * from 表 where dbo.f_getpy(字段)='zgyh'
本文出自：億恩科技【mszdt.com】