SQL Contains table and keword position in string

Have a question about full text search.

I have a query that works, but I want to improve it. For example, suppose I'm searching for "best solution". In my result (using FTS,CONTAINSTABLE,NEAR,ISABOUT,*) I have the columns rank, id, string:

rank| id | string
----+----+-------
430 | 33 | my own best solution
430 | 32 | the way for best solution
430 | 30 | the best solution sample
430 | 31 | best solution 
430 | 34 | best solution creation how
300 | 开发者_运维知识库40 | usefull best software solution
300 | 41 | best software solution
200 | 50 | wolrds bests solutions
200 | 51 | bests solutions of the world

So this query is 100% right for me, and all RANKs are correct, but I want to make this query more relevant.

If a keyword's position in a string is more to the left, it should appear earlier in the result. For example:

rank| id | string
----+----+-------
430 | 31 | best solution 
430 | 34 | best solution creation how
430 | 30 | the best solution sample
430 | 33 | my own best solution
430 | 32 | the way for best solution
300 | 41 | best software solution
300 | 40 | usefull best software solution
200 | 51 | bests solutions of the world
200 | 50 | wolrds bests solutions

Is this possible? If so, how can I get this result?

---

You need an indexOf function. Search your sql manual for a similar function.

Then add a sorting criteria looking like ORDER BY rank, CASE indexOf(string, 'best') WHEN -1 THEN 100000 ELSE indexOf(string, 'best') END.

If you can't find a function doing a kind of indexOf, write it yourself using the CREATE FUNCTION feature of your database manager.

---

The third party solution you want is Sphinx (there are still others). Read more about here:

http://www.ioncannon.net/programming/685/full-text-search-with-sphinx/