SQL: Sorting By Email Domain Name
domain-name, email, sorting, sql, sql-order-by
Solution
Try this
Query(For Sql Server):
select * from mytbl
order by SUBSTRING(email,(CHARINDEX('@',email)+1),1)
Query(For Oracle):
select * from mytbl
order by substr(email,INSTR(email,'@',1) + 1,1)
Query(for MySQL)
pygorex1 already answered
Output:
id name email
5 Tarrack Ocama me@am-no-president.org
3 Ali Baba ali@babaland.com
1 John Doe johndoe@domain.com
2 Jane Doe janedoe@helloworld.com
4 Foo Bar foo@worldof.bar.net
Problem
What is the shortest and/or efficient SQL statement to sort a table with a column of email address by it's DOMAIN name fragment? That's essentially ignoring whatever is before "@" in the email addresses and case-insensitive. Let's ignore the internationalized domain names for this one. Target at: mySQL, MSSQL, Oracle Sample data from `TABLE1` ``` id name email ------------------------------------------ 1 John Doe johndoe@domain.com 2 Jane Doe janedoe@helloworld.com 3 Ali Baba ali@babaland.com 4 Foo Bar foo@worldof.bar.net 5 Tarrack Ocama me@am-no-president.org ``` Order By Email `SELECT * FROM TABLE1 ORDER BY EMAIL ASC` ``` id name email ------------------------------------------ 3 Ali Baba ali@babaland.com 4 Foo Bar foo@worldof.bar.net 2 Jane Doe janedoe@helloworld.com 1 John Doe johndoe@domain.com 5 Tarrack Ocama me@am-no-president.org ``` Order By Domain `SELECT * FROM TABLE1 ORDER BY ?????? ASC` ``` id name email ------------------------------------------ 5 Tarrack Ocama me@am-no-president.org 3 Ali Baba ali@babaland.com 1 John Doe johndoe@domain.com 2 Jane Doe janedoe@helloworld.com 4 Foo Bar foo@worldof.bar.net ``` EDIT: I am not asking for a single SQL statement that will work on all 3 or more SQL engines. Any contribution are welcomed. :)