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