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

I have a MySQL table with values, for example:

+--------------+
| user_email   |
+--------------+
| ab@gmail.com |
| cd@gmail.com |
| ef@yahoo.com |
| gh@yahoo.com |
| ij@gmail.com |
| kl@other.net |
+--------------+

I need to return a list of unique domain names from this list of email addresses, for example:

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 solves only half of my problem - it returns domain names. Using DISTINCT did not help. What am I missing?

FYI: runs on the LAMP stack. Thank!

+5
source share
1 answer

Just use a group

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

All Articles