SQL学习----关键词搜索

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.youkuaiyun.com）为中心,建立了庞大的专业用户群, 形成了网站和期刊杂志、专业出版、电子商务、企业服务、信息服务，教育培训等关联业务互动的商业模式。')
--*/
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