Searching SQL Server

Question

I've been asked to put together a search for one of our databases.

The criteria is the user types into a search box, SQL then needs to split up all the words in the search and search for each of them across multiple fields (Probably 2 or 3), it then needs to weight the results for example the result where all the words appear will be the top result and if only 1 word appears it will be weighted lower.

For example if you search for "This is a demo post"

The results would be ranked like this

Rank   Field1                 Field2
1:     "This is a demo post"  ""
2:     "demo post"            ""
3:     "demo"                 "post"
4:     "post"                 ""

Hope that makes some sort of sense, its kind of a base Google like search.

Anyway I can think of doing this is very messy.

Any suggestions would be great.

Answer 1

Logically you can do this reasonably easily, although it may get hard to optimise - especially if someone uses a particularly long phrase.

Here's a basic example based on a table I have to hand...

SELECT TOP 100 Score, Forename FROM
(
    SELECT
     CASE
      WHEN Forename LIKE '%Kerry James%' THEN 100
      WHEN Forename LIKE '%Kerry%' AND Forename LIKE '%James%' THEN 75
      WHEN Forename LIKE '%Kerry%' THEN 50
      WHEN Forename LIKE '%James%' THEN 50
     END AS Score,
     Forename
    FROM
     tblPerson
) [Query]
WHERE
    Score > 0
ORDER BY
    Score DESC

In this example, I'm saying that an exact match is worth 100, a match with both terms (but not together) is worth 75 and a match of a single word is worth 50. You can make this as complicated as you wish and even include SOUNDEX matches too - but this is a simple example to point you in the right direction.

Answer 2

"Google-like search" means: fulltext search. Check it out!

• Understanding fulltext indexing on SQL Server
• Understanding SQL Server full-text indexing
• Getting started with SQL Server 2005 fulltext searching
• SQL Server fulltext search: language features

With SQL Server 2008, it's totally integrated into the SQL Server engine.

Before that, it was a bit of a quirky add-on. Another good reason to upgrade to SQL Server 2008! (and the SP1 is out already, too!)

Marc

Answer 3

I ended up creating a full text index on the table and joining my search results to FREETEXTTABLE, allowing me to see the ranked value of each result

The SQL ended up looking something like this

SELECT
    Msgs.RecordId,
    Msgs.Title,
    Msgs.Body
FROM
    [Messages] AS Msgs
    INNER JOIN FREETEXTTABLE([Messages],Title,@SearchText) AS TitleRanks ON Msgs.RecordId = TitleRanks.[Key]  
ORDER BY
    TitleRanks.[Key] DESC

I've used full text indexes in the past but never realised you could use FullTextTable like that, was very impressed with how easy it was to code and how well it works.