SQL Query to rank relevance of search results.

[MartinSmithhh]

I am currently writing an app which must have a free text search facility on a database field it also must allow wild card searches. But I am struggling on the most efficient way of doing this.

So if someone searches a Title Field for "*elcom* *To* *Taf*" I am splitting this into component words, converting * to % and generating a query.

Whilst this would be straight forward enough for a search of fields containing all these words I must also allow OR searching!

I would like the results to come back in order of relevance so that "Welcome To Taffy" (which matches all 3 search terms) will be ordered well above "Staff Update" (which matches only 1)

What I have so far works but I am sure would shock a SQL guru!


--------------------------------------------------------------------------------

SELECT TITLE, 3 [Matches]
FROM motd
WHERE ((TITLE LIKE '%ELCOM%') AND (TITLE LIKE '%TO%') AND 
    (TITLE LIKE '%TAF%'))
UNION
SELECT TITLE, 2 [Matches]
FROM motd
WHERE ((TITLE LIKE '%ELCOM%') AND (TITLE LIKE '%TO%')) OR
    ((TITLE LIKE '%ELCOM%') AND (TITLE LIKE '%TAF%')) OR
    ((TITLE LIKE '%TAF') AND (TITLE LIKE '%TO%'))
UNION
SELECT TITLE, 1 [Matches]
FROM motd
WHERE ((TITLE LIKE '%ELCOM%') OR
    (TITLE LIKE '%TO%') OR
    (TITLE LIKE '%TAF%'))
ORDER BY Matches

--------------------------------------------------------------------------------


The length and complexity of the query I need to generate grows exponentially with the number of words entered by the user, so even if I limited them to 10 words the resulting query would still look pretty horrific!

I have also read that the "Like" Keyword uses table scans which are apparently prohibitively resource intensive. Unfortunately this app also needs to be pretty scalable. I would welcome any suggestions for tackling this problem.

 

[RickCole]

If you are running SQL Server 7.0 or higher have a look under the topic Full-Text Searching in book on-line.

Sounds like it might be what your looking for.

Rick.