FTS results page question

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
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
"William Wise" <will@.digitalelite.com> wrote in message
news:Xns951582AA063F8willdigitalelitecom@.68.1.17.6 ...
> 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
|||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
"John Kane" <jt-kane@.comcast.net> wrote in
news:e6LiFCIXEHA.1684@.tk2msftngp13.phx.gbl:

> 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