ntext搜索关键字

/*--ntext搜索

 按 tb 表中的 keyword 在 ta 中查找 content
 列出每个 keyword 在 content 中的具体位置
--邹建 2004.07(引用请保留此信息)--*/

--测试数据
create table ta(id int identity(1,1),content ntext)
insert ta select '我是中国人我是中国人'
union all select '中国人民爱中国 中国人民爱中国 中国人民爱中国 中国人民爱中国'

create table tb(keyword nvarchar(100))
insert tb select '中'
union all select '中国'
go

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

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

--创建处理的存储过程
create proc p_search
as
create table #t(id int,keyword nvarchar(100),position int)

declare @s Nvarchar(4000),@keyword nvarchar(100)
declare @id int,@i int,@ilen int

declare tb cursor local for
select a.id,b.keyword,position=charindex(b.keyword,a.content)-1,ilen=4000-len(b.keyword)
from ta a,tb b
where charindex(b.keyword,a.content)>0

open tb
fetch tb into @id,@keyword,@i,@ilen
while @@fetch_status=0
begin
 select @s=substring(content,@i+1,4000)
 from ta where id=@id
 while @s<>''
 begin
  insert #t(id,keyword,position)
  select @id,@keyword,id+@i
  from 序数表
  where charindex(@keyword,@s,id)=id

  select @i=@i+@ilen,@s=substring(content,@i+1,4000)
  from ta where id=@id
 end
 
 fetch tb into @id,@keyword,@i,@ilen
end
close tb
deallocate tb
select * from #t
go

--调用示例
exec p_search
go

--删除测试
drop table 序数表,ta,tb
drop proc p_search

/*--测试结果

id          keyword   position 
----------- --------- ----------
1           中        3
1           中        8
1           中国      3
1           中国      8
2           中        1
2           中        6
2           中        9
2           中        14
2           中        17
2           中        22
2           中        25
2           中        30
2           中国      1
2           中国      6
2           中国      9
2           中国      14
2           中国      17
2           中国      22
2           中国      25
2           中国      30

（所影响的行数为 20 行）
--*/