The other day I wanted to know how many unique domain names existed in the emails addresses of one of our databases. I started looking for an easily way to find them and how common each domain was. The query below will give you those details.


select right(email, length(email) - locate('@', email)), count(*)
from Record 
where email is not null and email != ''
group by right(email, length(email) - locate('@', email))
order by count(*) desc;

There’s a lot going on here but the important part is the right(email, length(email) - locate('@', email)) calculation. Let’s break it down a little:

  • The right(email, x) function is taking the last x characters from the email address which should be the domain name.
  • In order to find the x for the right() function where using length(email) - locate('@', email) which is taking the total length of the email address and removing the number of characters up to the “@” sign.


I’ve removed the count(*) value because this was run on production data but it’s interesting to see the top providers:


Like this post? Don't forget to follow us on Twitter and Facebook for updates.