ntext搜索关键字

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

/*--ntext搜索

 按tb表中的keyword在ta中查找content 列出每个keyword在content中的具体位置--邹建2004.07--*/

--测试数据createtableta(idintidentity(1,1),contentntext)inserttaselect'我是中国人我是中国人'unionallselect'中国人民爱中国中国人民爱中国中国人民爱中国中国人民爱中国'

createtabletb(keywordnvarchar(100))inserttbselect'中'unionallselect'中国'go

/*=================处理========================*/ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[序数表]')andOBJECTPROPERTY(id,N'IsUserTable')=1)droptable[序数表]GO

--为了效率,所以要一个辅助表配合selecttop4000id=identity(int,1,1)into序数表fromsyscolumnsa,syscolumnsbaltertable序数表addconstraintpk_id_序数表primarykey(id)go

--创建处理的存储过程createprocp_searchascreatetable#t(idint,keywordnvarchar(100),positionint)

declare@sNvarchar(4000),@keywordnvarchar(100)declare@idint,@iint,@ilenint

declaretbcursorlocalforselecta.id,b.keyword,position=charindex(b.keyword,a.content)-1,ilen=4000-len(b.keyword)fromtaa,tbbwherecharindex(b.keyword,a.content)>0

opentbfetchtbinto@id,@keyword,@i,@ilenwhile@@fetch_status=0begin select@s=substring(content,@i+1,4000) fromtawhereid=@id while@s<>'' begin  insert#t(id,keyword,position)  select@id,@keyword,id+@i  from序数表  wherecharindex(@keyword,@s,id)=id

  select@i=@i+@ilen,@s=substring(content,@i+1,4000)  fromtawhereid=@id end  fetchtbinto@id,@keyword,@i,@ilenendclosetbdeallocatetbselect*from#tgo

--调用示例execp_searchgo

--删除测试droptable序数表,ta,tbdropprocp_search

/*--测试结果

id         keyword  position ------------------------------1          中       31          中       81          中国     31          中国     82          中       12          中       62          中       92          中       142          中       172          中       222          中       252          中       302          中国     12          中国     62          中国     92          中国     142          中国     172          中国     222          中国     252          中国     301 <script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
阅读更多
文章标签: 测试 object 存储
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭