Re: counting text matches - any recipes?

Ross,

> For the basic interface, we have the user types some words into a textbox,
> then just search 'everything'. I'm weighting the results, so that hits
> on the title count more than on keywords, which count more than on the
> abstract, which count more than in the body.

Before you re-invent the wheel, have you checked out OpenFTS? www.openfts.org
(I think).
>
> This is working reasonably well, but I'm stuck on one counting problem:
> what's the natural way to count multiple matches in SQL?

Within a single text field? There isn't.

2 choices:

1) Use a Full Text Searching engine, such as the simple one in /contrib or a
more full-featured one like OpenFTS. Both will allow you to do counts on
"hits" for a keyword.

2) Write a function in PL/perl which will count the number of keyword matches
in a text string. Potentially slower than #1, but easier to implement.

--
-Josh Berkus
Aglio Database Solutions
San Francisco