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 [email protected] 2 Jane Doe [email protected] 3 Ali Baba [email protected] 4 Foo Bar [email protected] 5 Tarrack Ocama [email protected]
Order By Email
SELECT * FROM TABLE1 ORDER BY EMAIL ASC
id name email ------------------------------------------ 3 Ali Baba [email protected] 4 Foo Bar [email protected] 2 Jane Doe [email protected] 1 John Doe [email protected] 5 Tarrack Ocama [email protected]
Order By Domain
SELECT * FROM TABLE1 ORDER BY ?????? ASC
id name email ------------------------------------------ 5 Tarrack Ocama [email protected] 3 Ali Baba [email protected] 1 John Doe [email protected] 2 Jane Doe [email protected] 4 Foo Bar [email protected]
EDIT:
I am not asking for a single SQL statement that will work on all 3 or more SQL engines. Any contribution are welcomed. :)
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 [email protected]
3 Ali Baba [email protected]
1 John Doe [email protected]
2 Jane Doe [email protected]
4 Foo Bar [email protected]