FREETEXT results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shank - 10 Jul 2004 18:52 GMT

Using the following query I get my desired results. However, in the
resulting recordset, how can I show for example 10 words before and 10 words
after the keyword or phrase that was searched upon? If I get 20 resulting
records I think it would be easier for the user to decide which they want to
view. I know Google does this. Is this an SQL issue or ASP issue?
thanks

DECLARE @SearchString varchar(100)
SET @SearchString = ' "stress" '
SELECT KEY_TBL.RANK, Title, Body
FROM Articles INNER JOIN
FREETEXTTABLE(Articles,*, @SearchString) AS KEY_TBL
ON Articles.ID = KEY_TBL.[KEY]
ORDER BY Rank DESC

Reply to this Message

Hilary Cotter - 10 Jul 2004 20:19 GMT

string handling is more efficiently done on the client level as opposed to
within SQL Server.

> Using the following query I get my desired results. However, in the
> resulting recordset, how can I show for example 10 words before and 10 words
[quoted text clipped - 10 lines]
> ON Articles.ID = KEY_TBL.[KEY]
> ORDER BY Rank DESC

Reply to this Message

John Kane - 12 Jul 2004 06:43 GMT

Hilary,
While in some cases, client-side process might be better, for example
client-side paging and sorting of results, however, in this case, I must
respectively disagree...

Shank, you can use Substring and PatIndex along with your FREETEXTTABLE
query and get the "Goggle like" results that you have requested.
Specifically, the following SQL FTS query on the pubs table pub_info will
return rows that match the FTS search word (books) and display the near by
words from 20 characters before the searched keyword(books) for a total
length of 100 characters.

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

Regards,
John

> string handling is more efficiently done on the client level as opposed to
> within SQL Server.
[quoted text clipped - 15 lines]
> > ON Articles.ID = KEY_TBL.[KEY]
> > ORDER BY Rank DESC

Reply to this Message

Hilary Cotter - 12 Jul 2004 22:28 GMT

I really don't think so. In fact I know so. You can easily demonstrate this
within vbscript.

In fact I have written and ISAPI extension that does this - and posted it
here. Thinking about doing it within a database is simply not a good choice.

> Hilary,
> While in some cases, client-side process might be better, for example
[quoted text clipped - 36 lines]
> > > ON Articles.ID = KEY_TBL.[KEY]
> > > ORDER BY Rank DESC

Reply to this Message

John Kane - 13 Jul 2004 00:58 GMT

Hi Hilary,
Then I guess we can agree that we disagree <G>, as I did say in " some
cases" and not in all cases, so we can agree, on a case-by-case basis. Are
you saying that in all cases, that all types of "string handling" are "bad"
if handled on the server-side?

Regards,
John

> I really don't think so. In fact I know so. You can easily demonstrate this
> within vbscript.
[quoted text clipped - 43 lines]
> > > > ON Articles.ID = KEY_TBL.[KEY]
> > > > ORDER BY Rank DESC

Reply to this Message