Let's say I have a table named carros .
In this table I have some cars registered. Ex:
ID | Name
1 | Corsa Amarelo
2 | Camaro 2 Portas Amarelo
3 | Camaro V12 Amarelo
4 | Camaro Esportivo Amarelo
5 | Fusca Amarelo
6 | Camaro Amarelo
7 | Gol Amarelo
8 | Ferrai Vermelha
9 | Ferrari 490 cv vermelha
If I use the following query SELECT * FROM carros WHERE carros_name LIKE %camaro amarelo% I will only result in the following line
6 | Camaro Amarelo
If I only split the search terms this way carros_name LIKE %camaro amarelo% OR carros_name LIKE %camaro% OR carros_name LIKE %amarelo% the result comes mixed.
But I would like to get as a result anything that has the words of the search term, organizing the results by the most similar term onwards. Something like:
ID | Name
1 | Camaro Amarelo
4 | Camaro V12 Amarelo
5 | Camaro Esportivo Amarelo
3 | Camaro 2 Portas Amarelo
2 | Corsa Amarelo
6 | Fusca Amarelo
7 | Gol Amarelo
As you can see in the above result, the result comes with a ranking where the searched term appears first, results with all search words in 2 seconds and lastly any result that has any word of the searched term.