lundi 20 juin 2016

SQL: Sorting By Email Domain Name

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. :)

Aucun commentaire:

Enregistrer un commentaire