Rid Iculous Rid Iculous - 6 months ago 10
SQL Question

Using DISTINCT() on a sub-string to group domains on an email DB in MySQL

I have a large MySQL DB of email addresses. I'd like to be able to group/filter them by domain. Is there a way of doing it using DISTINCT()?
At the moment I'm fetching all via PHP, iterate and split at the @-sign and create an array for each domain. This works fine for smaller data-sets, but is not viable in the long run. Any suggestions?

I'm envisaging something along those lines:

SELECT DISTINCT([part-of-email-after-the-@-sign].EMAIL) FROM CONTACTS


Thanks

Answer

You can use SUBSTRING_INDEX to find the part after @, and then use DISTINCT on that.

SELECT DISTINCT SUBSTRING_INDEX(user_email, '@', -1) AS domain
FROM Contacts
Comments