How to sort by the relevance of Like?

Question

I’m making an autocomplete that makes a request to a url, which in turn queries in the database.

This consultation is made with the LIKE. I use %$termo% to be able to consult, but I would like to sort the result according to the relevance of the LIKE, and not in alphabetical order.

How can I do that?

Example: When searching car, the order being something like that:

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

• Carrthe
• carthe
• reforcar
• lapcar

That is the order not by alphabetical order, but by the proximity of characters that are at the beginning of the word...

Answer 1

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

SET @termo := 'car';

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

Explaining the 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 may be in any position of the campo;
• The clause ORDER BY will take into account the position where the searched text is in the column campo, returning first those that have the text car at the beginning of the text;

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

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

LOCATE

... Returns the position of the first Occurrence of substring replace in string str.

In free translation:

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

---

CONCAT

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

In free translation:

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

Answer 2

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

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

In this case it will return the number 3, since "car" is in the 3 position of the word "meucarrovermelho".

With this you can do your ordering initially by it and then alphabetically.

Your query would look like this:

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

Answer 3

How about using Case When in the Order By?

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

Sort text in ascending order where to start with car.

Observing: This solution works on Sql-Server, because I don’t have a server Mysql installed. Case of Mysql works a little different, so you need to adjust.