sqlmysqlsql-order-bycustom-sort

Order MySQL query results by column: first by values starting with a term, then containing a term, then alphabetically

My Table column "business" looks like this:

Michael Kors
Baltimore Michael Kors
Charlotte Michael Kors
Michael Texas
Kors Dallas
Michael Kors

I have to apply order by on this column on String 'Michael Kors', so the sorted result should be something like this:

Michael Kors
Michael Kors
Baltimore Michael Kors
Charlotte Michael Kors
Kors Dallas
Michael Texas

If the string contains substring Michael Kors, it should be on top in alphabetical order. So in the example above, 2 rows with exact match is on top and after that Baltimore and Charlotte is 3rd and 4th in alphabetical order. Not worried about other strings which does not contain the exact phrase Michael Kors.

I tried using Substring_Index but looks like it doesn't work well with substring with spaces.

Solution

  • You can have multiple levels of ordering:

    order by 
  locate('Michael Kors', business)=1 desc, 
  locate('Michael Kors', business)>0 desc, 
  business

    The first one sorts the exact matches to top, next level sort the rest of the matching rows and the third sorts all the rest.