In my database, I have a keywords field that stores a comma-delimited list of keywords.

For example, a Shrek doll might have the following keywords:

ogre, green, plush, hero, boys' toys

A "Beanie Baby" doll ( that happens to be an ogre ) might have:

beanie baby, kids toys, beanbag toys, soft, infant, ogre

(That's a completely contrived example.)

What I'd like to do is if the consumer searches for "ogre" I'd like the "Shrek" doll to come up higher in the search results.

My content administrator feels that if the keyword is earlier in the list, it should get a higher ranking. ( This makes sense to me and it makes it easy for me to let them control the search result relevance ).

Here's a simplified query:

SELECT
p.ProductID         AS ContentID
, p.ProductName     AS Title
, p.ProductCode     AS Subtitle
, 100               AS Rank
, p.ProductKeywords AS Keywords
FROM Products AS p
WHERE FREETEXT( p.ProductKeywords, @SearchPredicate )

I'm thinking something along the lines of replacing the RANK with:

, 200 - INDEXOF(@SearchTerm)            AS Rank

This "should" rank the keyword results by their relevance

I know INDEXOF isn't a SQL command... but it's something LIKE that I would like to accomplish.

Am I approaching this the right way?

Is it possible to do something like this?

Does this make sense?