WEB开发网
开发学院数据库MSSQL Server Sql server如何得到汉字的声母 阅读

Sql server如何得到汉字的声母

 2007-05-31 15:23:13 来源:WEB开发网   
核心提示:在sql中创建用户自定义拼音函数:create function f_GetPy(@Str nvarchar(400))returns nvarchar(4000)asbegindeclare @strlen int,@re nvarchar(4000)declare @t table(chr nchar(1) col

在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'

Tags:Sql server 如何

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接