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
> 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