35.关键字搜索的函数

不念不忘少年蓝@ 2024-02-19 17:51 74阅读 0赞

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数据中,搜索以空格分隔的关键字
    	搜索到的关键字如果出现了多次,则只取最早出现的一次
    	搜索到的关键字前后保留的字符数据,根据函数中定义的总字符数/关键字个数决定
    
    */
    
    /*--调用示例
    
    	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