Mysql order by using search string

i have a mysql query like:

select * f开发者_如何学运维rom 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.

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)

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

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;

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