SQL: Sorting By Email Domain Name
Try this
Query(For Sql Server):
select * from mytblorder by SUBSTRING(email,(CHARINDEX('@',email)+1),1)
Query(For Oracle):
select * from mytblorder 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.org3 Ali Baba ali@babaland.com1 John Doe johndoe@domain.com2 Jane Doe janedoe@helloworld.com4 Foo Bar foo@worldof.bar.net
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;
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.