SELECT name
FROM table_test
WHERE name LIKE '%строка_поиска%'
ORDER BY CASE 
WHEN name LIKE 'строка_поиска' THEN 0
WHEN name LIKE 'строка_поиска %' THEN 1
WHEN name LIKE 'строка_поиска%' THEN 2
WHEN name LIKE '% строка_поиска%' THEN 3
ELSE 4
END

строка_поиска
строка_поиска и затем какой-то текст
строка_поиска_как_часть_слова
какой-то текст строка_поиска_как_часть_слова
прочие результаты, содержащие в_конце_слова_строка_поиска
или в_середине_строка_поиска_как_часть_слова