How to sort a MySQL table by email address domain

Peter Rukavina

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)

Comments

Submitted by stan on

Permalink

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

Submitted by Cody Caughlan on

Permalink

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.

Submitted by Daniel on

Permalink

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

Submitted by phoenix face on

Permalink

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.

Submitted by Joe Winett on

Permalink

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.

Submitted by Joe Winett on

Permalink

Oops. Should have been: SELECT * FROM Users ORDER BY SUBSTR( userEmail, LOCATE(‘@’,userEmail))

Submitted by Andrew X on

Permalink

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

Plain text

  • Allowed HTML tags: <b> <i> <em> <strong> <blockquote> <code> <ul> <ol> <li>
  • Lines and paragraphs break automatically.

About This Blog

Photo of Peter RukavinaI am . I am a writer, letterpress printer, and a curious person.

To learn more about me, read my /nowlook at my bio, 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 receive a daily digests of posts by email.

Search