How to Sort Results Order by Best Match using LIKE in MySQL

When you use LIKE operator to search and fetch the matched results from the database, the records are selected based on their entry. On another hand, the ORDER BY keyword allows you to sort the result-set in ascending or descending order based on a specific column. But the ascending or descending order is not useful on a search result with the LIKE operator. Because the result-set will not be relevance with ORDER BY in MySQL.

In LIKE search, the results must be sorted based on the word matches to make the result-set more relevance. Here we will show you how to sort results by best match using LIKE in MySQL.

The following SQL query will sort MySQL results order by best match in the LIKE search. The matched results will be sorted by the below order:

• Exact match with search word.
• Starting with search word.
• Search word in the middle.
• Ending with search word.
• Search word in any position.

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

In the example SQL query, the following are assumed:

• Database table name – words
• Column name – word
• Search keyword – searchstring