Search code examples
mysqlsql

How to use MySQL like with order by exact match first

I'm using MySQL 5.5 so that's why I can't use FULLTEXT search so please don't suggest it.

What I wanted to do is if user I have 5 records for example :

Amitesh
Ami
Amit
Abhi
Arun

And if someone searches for “Ami” then it should returns “Ami” first as exact match then “Amit” and “Amitesh.”

Solution

  • You can do:

    SELECT *
FROM TABLE t
WHERE col LIKE '%Ami%'
ORDER BY (col = 'Ami') DESC, LENGTH(col);