Порядок MySQL по "наилучшему совпадению"

У меня есть таблица, содержащая слова и поле ввода для поиска в этой таблице с помощью прямого поиска. В настоящее время я использую следующий запрос для поиска в таблице:

SELECT word FROM words WHERE word LIKE '%searchstring%' ORDER BY word ASC

Есть ли способ упорядочить результаты так, чтобы те, где строка находилась в начале слова, были первыми, а те, где строка появляется позже в слове, приходят последним?

Пример: поиск ' hab' возвращает

a lphabet
h abit
r ehab

но мне бы хотелось:

hab (сначала потому, что "hab" - это начало)
alp hab et (второй, потому что "hab" находится посреди слова)
re hab (последний, потому что "hab" находится в конце слова)

или по крайней мере таким образом:

hab (сначала потому, что "hab" - это начало)
re hab (второй, потому что "hab" начинается с третьей буквы)
alp hab et (последний, потому что "hab" начинается последним, на четвертой букве)

Было бы здорово, если бы кто-нибудь мог мне помочь!

Ответ 1

Чтобы сделать это первым способом (начинает слово, в середине слова, заканчивается слово), попробуйте что-то вроде этого:

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

Чтобы сделать это вторым способом (позиция совпадающей строки), используйте функцию LOCATE:

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

Вы также можете использовать тай-брейк в случае, например, более чем с одним словом начинается hab. Для этого я предлагаю:

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

В случае нескольких слов, начинающихся с hab, слова, начинающиеся с hab, будут сгруппированы и отсортированы в алфавитном порядке.

Ответ 2

Попробуйте следующим образом:

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

Ответ 3

Вы можете использовать функцию INSTR, чтобы вернуть начальную позицию строки поиска в слове,

 ORDER BY INSTR(word,searchstring)

Чтобы сделать результирующий набор более детерминированным, когда поисковая строка появляется в одной и той же позиции двумя разными словами, добавьте второе выражение в ORDER BY:

 ORDER BY INSTR(word,searchstring), word

(Например, searchstring hab появляется во второй позиции как chablis, так и shabby)