SQL Server:关键字搜索

 

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

if exists (select * from dbo.sysobjects where id = object_id(N'[序数表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [序数表]
GO

--为了效率,所以要一个辅助表配合
select top 2000 id=identity(int,1,1) into 序数表 
from syscolumns a,syscolumns b
alter table 序数表 add constraint pk_id_序数表 primary key(id)
GO

/*--关键字搜索的函数
    
    在ntext数据中,搜索以空格分隔的关键字
    搜索到的关键字如果出现了多次,则只取最早出现的一次
    搜索到的关键字前后保留的字符数据,根据函数中定义的总字符数/关键字个数决定

--邹建 2004.11(引用请保留此信息)--*/

/*--调用示例

    select dbo.f_search('北京 公司 网站 软件 IT WWW','北京百联美达美数码科技有限公司，是面向IT专业技术人员和软件开发及应用企业，以专业社区为中心的IT专业知识传播与服务商。公司以IT专业网站（www.CSDN.net）为中心,建立了庞大的专业用户群, 形成了网站和期刊杂志、专业出版、电子商务、企业服务、信息服务，教育培训等关联业务互动的商业模式。')
--*/
create function f_search(
@keyword nvarchar(2000),
@text ntext
)returns nvarchar(4000)
as
begin
    declare @relen int
    set @relen=120    --搜索结果返回的总长度(不包含关键字自身长度)
        --根据这个总长度/搜索的关键字个数,决定关键字前后包含的字符数

    declare @t table(keyword nvarchar(120),skey nvarchar(120))
    declare @r nvarchar(800),@i int,@ilen int

    --分拆关键字列表
    insert @t select substring(@keyword,id,charindex(' ',@keyword+' ',id)-id)
        ,'%'+substring(@keyword,id,charindex(' ',@keyword+' ',id)-id)+'%'
    from 序数表
    where id<=len(@keyword)+1 and charindex(' ',' '+@keyword,id)-id=0
    
    --关键字前后要取的字符数(如果关键字前后的字符数是固定的,则直接为@ilen赋值)
    select @ilen=@relen/count(*) from @t
    --如果没有搜索的关键字,或者无法取得前后的值,则直接退出
    if @ilen=0 return('')

    --取关键字
    declare @p1 int,@plen int
    select @r='',@i=null
    select 
        @p1=case
            when @i>=pos-@ilen then @i
            when pos<@ilen then 1
            else pos-@ilen end,
        @plen=case
            when @i>=pos-@ilen then @ilen+keylen-@i+pos
            when pos<@ilen then @ilen+keylen+pos
            else @ilen*2+keylen end,
        @r=@r+case
            when @i>=pos-@ilen then ''
            else '...' end
            +substring(@text,@p1,@plen),
        @i=@p1+@plen
    from(
        select top 100 keyword,pos=patindex(skey,@text),keylen=len(keyword)
        from @t
        where patindex(skey,@text)>0
        order by pos
    )a

    return(@r+'...')
end
go