MySQL order by "best match"

Question

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!

score 118 · Accepted Answer · answered Sep 10 '13 at 18:15

118

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.

answered Sep 10 '13 at 18:15

Ed Gibbs

25,924
4
46
69

Great answers to one problem where each solution is useful to know/learn. – Nitin Apr 14 '19 at 18:24
2

I would recommend adding another case `WHEN word like 'searchstring' THEN 0` (without `%`) to capture the cases of exact matches separately. – gtmsingh Jan 27 '21 at 09:02
If it's an exact match for `searchstring` it should naturally sort to the top if you use the tie-breaker of `ORDER BY , word`. Example: searching for `so` returns sob, son, soap, socks, so, sop, sow. Ordering by *CASE WHEN word LIKE 'searchstring%' THEN 1 WHEN word LIKE '%searchstring' THEN 3 ELSE 2 END, **word*** would bring the exact match (`so`) to the top. Does that make sense? – Ed Gibbs Jan 28 '21 at 02:13

Robert · Answer 2 · 2013-09-10T18:17:09.713

27

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

edited Sep 10 '13 at 18:17

answered Sep 10 '13 at 18:06

Robert

25,425
8
67
81

I like the idea of picking the words that start and end with 'searchstring' and I would be fine with just ordering those with 'searchstring' in the middle alphabetically. However, this is not quite right. – eevaa Sep 10 '13 at 18:14
It takes too much time in large data set – Krunal Shah May 14 '18 at 07:30
@KrunalShah `Order by` slows down queries in general – Robert May 14 '18 at 08:34
2

I don't see how the CASE expression shown in this answer would ever return 3. Any value of word that satisfies that condition would have already satisfied the previous condition, and the CASE expression would return 2 ... maybe the checks should be in a different order? – spencer7593 May 30 '20 at 00:50

score 11 · Answer 3 · answered Sep 10 '13 at 18:20

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)

score 1 · Answer 4 · answered Dec 27 '21 at 08:17

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

score 1 · Answer 5 · answered Oct 06 '22 at 19:21

I got the best match for multiple columns using this query:

SELECT
    id,
    wordA,
    wordB,
   (CASE
        WHEN wordA = 'keywordA%' THEN 0
        WHEN wordA LIKE 'keywordA%' THEN 1
        WHEN wordA LIKE '%keywordA%' THEN 2
        WHEN wordA LIKE '%keywordA' THEN 3
        ELSE 4
    END) AS 'wordA_keywordA_score',
   (CASE
        WHEN wordA = 'keywordB%' THEN 0
        WHEN wordA LIKE 'keywordB%' THEN 1
        WHEN wordA LIKE '%keywordB%' THEN 2
        WHEN wordA LIKE '%keywordB' THEN 3
        ELSE 4
    END) AS 'wordA_keywordB_score',
   (CASE
        WHEN wordB = 'keywordA%' THEN 0
        WHEN wordB LIKE 'keywordA%' THEN 1
        WHEN wordB LIKE '%keywordA%' THEN 2
        WHEN wordB LIKE '%keywordA' THEN 3
        ELSE 4
    END) AS 'wordB_keywordA_score',
   (CASE
        WHEN wordB = 'keywordB%' THEN 0
        WHEN wordB LIKE 'keywordB%' THEN 1
        WHEN wordB LIKE '%keywordB%' THEN 2
        WHEN wordB LIKE '%keywordB' THEN 3
        ELSE 4
    END) AS 'wordB_keywordB_score'
FROM
    words
WHERE
    wordA like '%keywordA%'
OR
    wordA like '%keywordB%'
OR
    wordB like '%keywordA%'
OR
    wordB like '%keywordB%'
ORDER BY 
    (
        wordA_keywordA_score + 
        wordA_keywordB_score + 
        wordB_keywordA_score + 
        wordB_keywordB_score
    )
ASC;

MySQL order by "best match"

5 Answers5

Linked

Related