SQL: Sorting By Email Domain Name

Question

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 1

For MySQL:

select email, SUBSTRING_INDEX(email,'@',-1) AS domain from user order by domain desc;

For case-insensitive:

select user_id, username, email, LOWER(SUBSTRING_INDEX(email,'@',-1)) AS domain from user order by domain desc;

Answer 2

You are going to have to use the text manipulation functions to parse out the domain. Then order by the new column.

Answer 3

Assuming you really must cater for MySQL, Oracle and MSSQL .. the most efficient way might be to store the account name and domain name in two separate fields. The you can do your ordering:

select id,name,email from table order by name

select id,name,email,account,domain from table order by email

select id,name,email,account,domain from table order by domain,account

as donnie points out, string manipulation functions are non standard .. that is why you will have to keep the data redundant!

I've added account and domain to the third query, since I seam to recall not all DBMSs will sort a query on a field that isn't in the selected fields.

Answer 4

MySQL, an intelligent combination of right() and instr()

SQL Server, right() and patindex()

Oracle, instr() and substr()

And, as said by someone else, if you have a decent to high record count, wrapping your email field in functions in you where clause will make it so the RDBMS can't use any index you might have on that column. So, you may want to consider creating a computed column which holds the domain.

Answer 5

If you have million records, I suggest you to create new column with domain name only.

Answer 6

If you want this solution to scale at all, you should not be trying to extract sub-columns. Per-row functions are notoriously slow as the table gets bigger and bigger.

The right thing to do in this case is to move the cost of extraction from select (where it happens a lot) to insert/update where it happens less (in most normal databases). By incurring the cost only on insert and update, you greatly increase the overall efficiency of the database, since that's the only point in time where you need to do it (i.e., it's the only time when the data changes).

In order to achieve this, split the email address into two distinct columns in the table, email_user and email_domain). Then you can either split it in your application before insertion/update or use a trigger (or pre-computed columns if your DBMS supports it) in the database to do it automatically.

Then you sort on email_domain and, when you want the full email address, you use email_name|'@'|email_domain.

Alternatively, you can keep the full email column and use a trigger to duplicate just the domain part in email_domain, then you never need to worry about concatenating the columns to get the full email address.

It's perfectly acceptable to revert from 3NF for performance reasons provided you know what you're doing. In this case, the data in the two columns can't get out of sync simply because the triggers won't allow it. It's a good way to trade disk space (relatively cheap) for performance (we always want more of that).

And, if you're the sort that doesn't like reverting from 3NF at all, the email_name/email_domain solution will fix that.

This is also assuming you just want to handle email addresses of the form a@b - there are other valid email addresses but I can't recall seeing any of them in the wild for years.

Answer 7

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]

Answer 8

For SQL Server, you could add a computed column to your table with extracts the domain into a separate field. If you persist that column into the table, you can use it like any other field and even put an index on it, to speed things up, if you query by domain name a lot:

ALTER TABLE Table1
  ADD DomainName AS 
     SUBSTRING(email, CHARINDEX('@', email)+1, 500) PERSISTED

So now your table would have an additional column "DomainName" which contains anything after the "@" sign in your e-mail address.

Answer 9

select id,name,email,substr(email,instr(email,'@',1)+1) as domain
from table1
order by domain asc
This will work with Oracle

Answer 10

work smarter not harder:

SELECT
REVERSE(SUBSTRING_INDEX(
REVERSE(SUBSTRING(
emails.email,
POSITION('@' IN emails.email)+1
)),
'.',
2))
FROM emails
http://www.compiatech.com