Mysql order by using search string

问题:

i have a mysql query like:

select * from employee 
  where name like '%ani%'

I want my results to be order by starting with ani for eg. my results should be comming as: {anil,anirudha,rani, ...} starting with ani first and then followed by other characters.

Can anybody please help me on this.

回答1:

Edit: Added the second solution.

Solution 1:

select  1 AS OrderField, name
from    employee 
where   name like 'ani%'
UNION ALL
select  2 AS OrderField, name
from    employee 
where   name like '_%ani%'
ORDER BY OrderField, name

Solution 2:

select  name, CASE WHEN name LIKE 'ani%' THEN 1 ELSE 2 END OrderField
from    employee
where   name like '%ani%'
order by OrderField, name

回答2:

Something like

select * from employee where name like '%ani%' order by locate('ani', name) asc, name asc

Should do the trick ... (sort by position of the substring in the whole string)

回答3:

You can possibly use a union to achieve this:

select * from employee where name like 'ani%' order by name
union 
select * from employee where name like '%ani%' and not name like 'ani%' order by name

回答4:

I am not entirely sure exactly what you are looking for. If you would like to just order the results of that query by name:

select * from employee 
  where name like '%ani%'
order by name asc;

回答5:

select  * from employee 
where name like '%'+'ani'+'%'
order by if(SUBSTRING(name, 1, length('ani'))= 'ani', 0,1), name

for mssql server repalce if with case when
with the syntax provided, is easy to replace 'ani' with any value you want