MySQL order by "best match"
MysqlSqlSql Order-ByMysql Problem Overview
I have a table that contains words and an input field to search that table using a live search. Currently, I use the following query to search the table:
SELECT word FROM words WHERE word LIKE '%searchstring%' ORDER BY word ASC
Is there a way to order the results so that the ones where the string is found at the beginning of the word come first and those where the string appears later in the word come last?
An example: searching for 'hab' currently returns
- a lphabet
- h abit
- r ehab
but I'd like it this way:
- hab it (first because 'hab' is the beginning)
- alp hab et (second because 'hab' is in the middle of the word)
- re hab (last because 'hab' is at the end of the word)
or at least this way:
- hab it (first because 'hab' is the beginning)
- re hab (second because 'hab' starts at the third letter)
- alp hab et (last because 'hab' starts latest, at the fourth letter)
Would be great if anyone could help me out with this!
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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
Solution 3 - Mysql
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
)
Solution 4 - Mysql
In your case it would be:
ORDER BY INSTR(word, '%searchstring%')
INSTR
search in the word
column for '%searchstring%'
and return it's position, if no match then it will be 0
and cause result go down in order.
You also can add DESC for changing direction, eg:
ORDER BY INSTR(word, '%searchstring%') DESC