How to sort by Like relevancy?

You can use the LOCATE function of MySQL within ORDER BY :

SET @termo := 'car';

SELECT *
  FROM tabela
 WHERE campo LIKE CONCAT('%', @termo, '%')
 ORDER BY LOCATE(@termo, campo);

Explaining query above:

• The variable @termo is created and receives the text that will be searched in the table;
The CONCAT will put % at the beginning and end of the text, resulting in %car% in the case of the example, which indicates that car can be in any position of campo ;
• The ORDER BY clause will take into account the position where the text searched for is in the campo column, first returning those with the text car at the beginning of the text;

The result will not be the same as the one shown in the example because the method presented above does not take into account only the beginning and the end of the campo column. It takes into account the position in the whole sentence presenting it as follows:

╔═══╦════════════╗
║   ║ campo      ║
╠═══╬════════════╣
║ 1 ║ caro       ║
║ 2 ║ carro      ║
║ 3 ║ colocar    ║
║ 4 ║ reforcar   ║
╚═══╩════════════╝

  

LOCATE      

... returns the position of the first occurrence of substring substr in string str .

Free translation:

  

... returns the position of the first occurrence of a substring substr in a string str .

  

CONCAT      

For quoted strings, concatenation can be performed by placing the strings next to each other.

Free translation:

  

For highlighted strings, concatenation can be performed by placing the strings one after the other.

You can do this by checking where that string is in your field, with the function INSTR , for example:

SELECT INSTR('meucarrovermelho','car'); 

In this case, you will return the number 3, since "car" is in the 3rd position of the word "redcar".

With this you can do your ordination initially by it and then in alphabetical order.

Your query would look like this:

SELECT * 
FROM tabela 
WHERE campo LIKE '%car%' 
ORDER BY INSTR(campo ,'car'), campo;

How about using Case When no Order By ?

 select * from MinhaTabela
 WHERE texto LIKE '%car%'
 order by
 case when texto like 'car%' then 0 else 1 end

Sort the text where you start with car upwards.

Note: This solution works on Sql-Server , since I do not have Mysql server installed. The Case of Mysql works a little differently then you need to adjust.