MySQL order by "best match"

Question

To do it the first way (starts word, in the middle of the word, ends word), try something like this:

SELECT word
FROM words
WHERE word LIKE '%searchstring%'
ORDER BY
  CASE
    WHEN word LIKE 'searchstring%' THEN 1
    WHEN word LIKE '%searchstring' THEN 3
    ELSE 2
  END

To do it the second way (position of the matched string), use the LOCATE function:

SELECT word
FROM words
WHERE word LIKE '%searchstring%'
ORDER BY LOCATE('searchstring', word)

You may also want a tie-breaker in case, for example, more than one word starts with hab. To do that, I'd suggest:

SELECT word
FROM words
WHERE word LIKE '%searchstring%'
ORDER BY <whatever>, word

In the case of multiple words starting with hab, the words starting with hab will be grouped together and sorted alphabetically.

Try this way:

SELECT word 
FROM words 
WHERE word LIKE '%searchstring%' 
ORDER BY CASE WHEN word = 'searchstring' THEN 0  
              WHEN word LIKE 'searchstring%' THEN 1  
              WHEN word LIKE '%searchstring%' THEN 2  
              WHEN word LIKE '%searchstring' THEN 3  
              ELSE 4
         END, word ASC

You could use the INSTR function to return the starting position of the search string within the word,

 ORDER BY INSTR(word,searchstring)

To make the resultset more deterministic when the searchstring appears in the same position in two different words, add a second expression to the ORDER BY:

 ORDER BY INSTR(word,searchstring), word

(For example, searchstring hab appears in second position of both chablis and shabby)