MySQL order by “best match”

by

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.

More Related Contents:

  1. How does MySQL process ORDER BY and LIMIT in a query?
  2. Ordering by the order of values in a SQL IN() clause
  3. Ordering by specific field value first
  4. MySQL “Group By” and “Order By”
  5. Using union and order by clause in mysql
  6. SQL order string as number
  7. Order by COUNT per value
  8. MySQL/MariaDB – order by inside subquery
  9. Mysql: Order by like?
  10. Sort the rows according to the order specified in WHERE IN clause
  11. ORDER BY alphabet first then follow by number
  12. MYSQL Select One Random record from each Category
  13. Recreating a query using INNER JOIN? [duplicate]
  14. Create a Cumulative Sum Column in MySQL
  15. Equivalent of explode() to work with strings in MySQL
  16. mysql select dynamic row values as column names, another column as value
  17. MySQL DELETE FROM with subquery as condition
  18. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  19. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  20. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  21. Simple Random Samples from a Sql database
  22. How do I use properly CASE..WHEN in MySQL
  23. When to use SELECT … FOR UPDATE?
  24. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  25. SQL unique varchar case sensitivity question
  26. How to regex in a MySQL query
  27. MySQL 5.6 – DENSE_RANK like functionality without Order By
  28. Syntax error on MERGE statement
  29. MySQL: Computed Column
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment