I have MySQL table that has an email address field, and I want to sort the table by the domain of the email address. I went looking for some complex substringy way of doing this when I came across this elegant solution: just sort by the reverse of the email address.
While this doesn’t actually sort the addresses so that, say, AOL addresses come first followed by Bellsouth, Comcast, Dartmouth, etc., it does group all the email addresses for the same domain together in order, which is all I really needed. If this is all you really need, then it’s as simple as:
select * from table order by reverse(email)
Hmm… how would that work? Would something like email@example.com vs. firstname.lastname@example.org get sorted wrong?
moc.loa@la <= Sorted after ma
This is a feasible solution if you only have a small number of rows, as no index can be used. That is, even if you do have an index on the email column, MySQL will not be able to use it.
I think your best bet is to parse out the domain in the application and then store the domain alongside the email in the DB and then sort on that — if you index the domain column then the index can be used and you dont need to worry about calling a non-deterministic MySQL function on a column.
Good one. very concise and useful.
In sql server I would do something like
select * from table order by substring(email, charindex(‘@’, email) + 1, len(email)), email
this will order first by domain, then by email. I guess MySQL has similar functions
I think adding a new “domain_name” column is the right way. The email address validation routine should be able to parse out the domain name.
In MySQL you can do:
SELECT * FROM Users WHERE SUBSTR( userEmail, LOCATE(‘@’,userEmail))
But, of course, this requires a table scan. It’s not a big deal if you don’t have loads of rows.
Oops. Should have been: SELECT * FROM Users ORDER BY SUBSTR( userEmail, LOCATE(‘@’,userEmail))
Just what I needed.
Folks, if you want to do this and also take advantage of indexing, just *store* the reversed email address, right from the beginning. Just be sure to give it a descriptive name, such as “reversed_email_address”.
While keeping an extra column just to store the (redundant) domain also allows indexing, it is wasteful of space.
Alternatively, if you don’t care about indexes (in which case, Shame on you!), and you don’t like storing the reversed email address, but still want to sort by domain (and with an actually correct lexicographical ordering), just do:
SELECT *, SUBSTRING( email_address, POSITION( “@” IN EMAIL_ADDRESS ) + 1 ) as domain FROM The_Table;