Header

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.

Query

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.

Output

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

'gmail.com'
'yahoo.com'
'hotmail.com'
'aol.com'
'icloud.com'
'comcast.net'
'outlook.com'
'live.com'
'ymail.com'
'msn.com'
'att.net'
'verizon.net'

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