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.
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:
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: