1

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

Rid Iculous
  • 3,696
  • 3
  • 23
  • 28
  • http://stackoverflow.com/questions/11805166/how-to-return-distinct-domain-names-from-email-address-values-in-mysql – George Pant May 12 '16 at 00:17

3 Answers3

3

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
Barmar
  • 741,623
  • 53
  • 500
  • 612
2

uses INSTR to find '@':

select DISTINCT substr(EMAIL, instr(EMAIL, '@') + 1) from CONTACTS;
Joao Polo
  • 2,153
  • 1
  • 17
  • 26
2

My first thought is substring, seems a few ways of doing this, included supporting links

Option 1

Maybe specific to SQL Server, confirming now

SELECT DISTINCT(RIGHT(Email, LEN(Email) - CHARINDEX('@', Email))) FROM CONTACTS

http://blog.sqlauthority.com/2011/06/18/sql-server-selecting-domain-from-email-address/

Option 2

As seen in How to select domain name from email address

I assume the extra SUBSTRING_INDEX is to remove the .com or ect

select (SUBSTRING_INDEX(SUBSTR(email, INSTR(email, '@') + 1),'.',1))

SELECT DISTINCT(SUBSTR(email, INSTR(email, '@') + 1)) FROM CONTACTS

Option 3

How to return distinct domain names from email address values in MySQL?

Makes use of GROUP BY rather than DISTINCT, possible performance factor there so worth testing

SELECT SUBSTRING_INDEX(user_email,'@',-1) as domain_name FROM user_email group by domain_name
Community
  • 1
  • 1
Daniel Brose
  • 1,394
  • 10
  • 24
  • I would be very surprised if there's any performance difference between `DISTINCT` and `GROUP BY` when you're grouping by all the columns in the `SELECT`. – Barmar May 12 '16 at 00:39