Sunday, March 25, 2012

additional match information from CONTAINSTABLE

Is there a way to get additional match information from SQL Server
2000 CONTAINSTABLE? For example:
If my text data is:
The black bird and the blue bird...
01234567890123456789012678901234
And I search for bird, Can I get results that tell me that 2
occurances were found inside the data at locations 10 and 21?
no, there is no way to do this. You can get some of this functionality if
you dump your database content into the file system and then use Indexing
Services hit highlighting features to mark up your content in a web page.
"madmike" <mikek@.cs.cmu.edu> wrote in message
news:cb16b300.0407061229.c1f707d@.posting.google.co m...
> Is there a way to get additional match information from SQL Server
> 2000 CONTAINSTABLE? For example:
> If my text data is:
> The black bird and the blue bird...
> 01234567890123456789012678901234
> And I search for bird, Can I get results that tell me that 2
> occurances were found inside the data at locations 10 and 21?
|||MadMike,
While Hilary is correct in that you cannot get the specific info you're
requesting from CONTAINSTABLE or even CONTAINS, you *might* get somewhat
close to your requirements via using PatIndex. For example, if you could
work with getting a range of words, plus and minus distance from the search
word, you could do something like the below query 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)
*/
This might start you thinking in other terms on how to get your
requirements, as they say there are many ways to skin a cat!
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uPD$qt6YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> no, there is no way to do this. You can get some of this functionality if
> you dump your database content into the file system and then use Indexing
> Services hit highlighting features to mark up your content in a web page.

> "madmike" <mikek@.cs.cmu.edu> wrote in message
> news:cb16b300.0407061229.c1f707d@.posting.google.co m...
>
sql

No comments:

Post a Comment