CODEWITHSUNDEEP
mysql
Jury A
Jury A

Reputation: 20062

How to use substring in order by

I have the following query to select the domain names that have three levels separated by two dots:

select domainname from db.table
where criteria like ('*.com')
AND domainname like ('%.%.%');

Now, I need to order the results by the .%.% part of the domain name and not %.%.%. How can I add this to my query ?

Upvotes: 1

Views: 15766

Answers (2)

linepogl
linepogl

Reputation: 9335

Check out the SUBSTRING_INDEX() function:

select domainname from db.table
where criteria like ('*.com')
AND domainname like ('%.%.%')
ORDER BY SUBSTRING_INDEX(domainname, '.', -2);

Upvotes: 4

xdazz
xdazz

Reputation: 160833

You could use the function SUBSTRING_INDEX

ORDER BY SUBSTRING_INDEX(domainname, '.', -2)

Upvotes: 2

Related Questions