MySQL Fulltext search result relevance

Question

I've the following 'banks' table

ID  | name

1   | JPMorgan Chase bank  
2   | Bank of south  
3   | Citigroup bank 
4   | Wells Fargo bank
5   | Bank of New York 

So when searches for 'bank of' I want the search result to return as follows:

Bank of New York
Bank of south
Citigroup bank
JPMorgan Chase bank
Wells Fargo bank

But with my code below it returns:

JPMorgan Chase bank
Bank of America
Citigroup bank
Wells Fargo bank
Bank of New York 

This is the code:

$search_term = 'bank of';

$sql2 = "SELECT *, 
         MATCH(name) AGAINST ('.$search_term.' IN BOOLEAN MODE)
         AS relevance FROM banks
         HAVING relevance > 0.8 
         ORDER BY relevance DESC";  

Thanks!

Answer 1

...right but what are my choices?

You can try something like this

SELECT *, 
       MATCH(name) AGAINST ('bank of' IN BOOLEAN MODE) AS relevance,
       name LIKE '%bank of%' AS full_match
  FROM banks
HAVING relevance > 0.8 
 ORDER BY full_match DESC, relevance DESC, name

Outcome:

| ID |                NAME | RELEVANCE | FULL_MATCH |
|----|---------------------|-----------|------------|
|  5 |    Bank of New York |         1 |          1 |
|  2 |       Bank of south |         1 |          1 |
|  3 |      Citigroup bank |         1 |          0 |
|  1 | JPMorgan Chase bank |         1 |          0 |
|  4 |    Wells Fargo bank |         1 |          0 |

Here is SQLFiddle demo