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)
About This Blog
I am Peter Rukavina and this is my blog. I am a writer, letterpress printer, and a curious person.
To learn more about me, read my /now, look at my bio, listen to audio I’ve posted, read presentations and speeches I’ve written, or get in touch (peter@rukavina.net is the quickest way).
You can subscribe to an RSS feed of posts, an RSS feed of comments, or a podcast RSS feed that just contains audio posts. You can also receive a daily digests of posts by email.
Comments
Hmm… how would that work?
Hmm… how would that work? Would something like ma@aol.com vs. al@aol.com get sorted wrong?
moc.loa@am
moc.loa@la <= Sorted after ma
This is a feasible solution
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
Good one. very concise and useful.
In sql server I would do
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
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 *
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:
Oops. Should have been: SELECT * FROM Users ORDER BY SUBSTR( userEmail, LOCATE(‘@’,userEmail))
Top Solution.Just what I
Top Solution.
Just what I needed.
Thx
Folks, if you want to do this
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;
Add new comment