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/