CODEWITHSUNDEEP
mysqlstringsql-likesql-order-by
jijo pidiyath
jijo pidiyath

Reputation: 31

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.

Upvotes: 3

Views: 3666

Answers (4)

Saic Siquot
Saic Siquot

Reputation: 6513

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

Upvotes: 0

John Kane
John Kane

Reputation: 4443

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;

Upvotes: 0

Ezekiel Rage
Ezekiel Rage

Reputation: 581

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)

Upvotes: 3

user949502
user949502

Reputation: 9

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

Upvotes: 0

Related Questions