CODEWITHSUNDEEP
mysqlselect
Mike Lee
Mike Lee

Reputation: 173

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

I have a MySQL table with values like:

+--------------+
| user_email   |
+--------------+
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
+--------------+

I need to return a list of unique domain names from this list of email address, such as:

gmail.com, yahoo.com, other.net

So far, I am using the following SQL statement to select this:

SELECT SUBSTRING_INDEX(user_email,'@',-1)

However, this only solves half of my problem - it is returning domain names. Using DISTINCT did not do the trick. What am I missing?

FYI: This is running on a LAMP stack. Thanks!

Upvotes: 7

Views: 9281

Answers (2)

Hemanth Kumar
Hemanth Kumar

Reputation: 819

SELECT split_part (email,'@',2) as domain_name FROM pytest.emps group by domain_name;

Upvotes: 0

Hawili
Hawili

Reputation: 1659

Just use group by 

SELECT SUBSTRING_INDEX(user_email,'@',-1) as domain_name FROM user_email group by domain_name

Upvotes: 18

Related Questions