SQL: Sorting By Email Domain Name

o.k.w picture o.k.w · Nov 28, 2009 · Viewed 23.9k times · Source

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

Answer

priyanka.sarkar picture priyanka.sarkar · Nov 28, 2009

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]