Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Full-Text Search / June 2004

Tip: Looking for answers? Try searching our database.

FTS results page question

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
William Wise - 27 Jun 2004 17:50 GMT
I'm an FTS newb so please be gentle.

When using most search services the results returned are accompanied by a
snippet or excerpt of the full text that scored a hit showing the matched
word(s)/phrase in context.  

For example, if I search for "full-text search" I might receive this as one
entry in the list of results:

... immediately alerts you if a server gets out ... a graphical
administration interface, an SQL query tool ... It provides the full-text
search based on Microsoft Indexing ...

Can I get a similar snippet or excerpt using FTS or will I have to roll my
own solution? Any suggestions regarding how to implement the latter would
be greatly appreciated.

Thanks,
Will
John Kane - 27 Jun 2004 20:54 GMT
Hi Will,
Not to worry, as I and others who post the replies here, know a great deal
about SQL Full-text Search (FTS ;-)
Could you post the output of the following SQL script that will provide info
on your SQL Server and OS platform?

use master
go
SELECT @@language
SELECT @@version
go

If I correctly understand your requirement, you want a range of words, plus
and minus distance from the search word. Correct?
Assuming so, then using a table (pub_info) in the Pubs database that is
already FT-enabled on the TEXT column (pr_info), you could use the following
SQL code to get the results you want:

-- The following SQL FTS query on the pubs table pub_info will return rows
that match the FTS search word (books)
-- and the near by words from 20 characters before to 100 characters after
the searched keyword(books).

SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
FROM pub_info
 WHERE Contains(pr_info, 'books')

/* returns the following results:
pub_id
------ ---------------------------------------------------------------------
-------------------------------
9952   t data for Scootney Books, publisher 9952 in the pubs database.
Scootney Books is located in New Yor
0736   t data for New Moon Books, publisher 0736 in the pubs database. New
Moon Books is located in Boston,

(2 row(s) affected)
*/

You can vary the length of the results via the PatIndex parameters.

Hopefully, this is what you're looking for!
Regards,
John

> I'm an FTS newb so please be gentle.
>
[quoted text clipped - 15 lines]
> Thanks,
> Will
William Wise - 28 Jun 2004 00:40 GMT
Hi John,

Here's the info:

us_english
Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05  
Copyright (c) 1988-2003 Microsoft Corporation  Developer Edition on Windows
NT 5.2 (Build 3790: )

Thanks for the quick reply.  Works like a charm.  I'll have to futze with
it some more to get it to prefix the pattern with <STRONG>tags</STRONG>.
Seems like this will require me to parse a complex user-submitted search
string if I want to show instances of each hit.

Will

> Hi Will,
> Not to worry, as I and others who post the replies here, know a great
[quoted text clipped - 41 lines]
> Regards,
> John
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.