How to select domain name from email address

Solution 1:

Assuming that the domain is a single word domain like gmail.com, yahoo.com, use

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

The inner SUBSTR gets the right part of the email address after @ and the outer SUBSTRING_INDEX will cut off the result at the first period.

otherwise if domain is expected to contain multiple words like mail.yahoo.com, etc, use:

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

LENGTH(email) - (INSTR(email, '@') + 1) - LENGTH(SUBSTRING_INDEX(email,'.',-1)) will get the length of the domain minus the TLD (.com, .biz etc. part) by using SUBSTRING_INDEX with a negative count which will calculate from right to left.

Solution 2:

I prefer:

select right(email_address, length(email_address)-INSTR(email_address, '@')) ...

so you don't have to guess how many sub-domains your user's email domain has.

Solution 3:

For PostgreSQL:

split_part(email, '@', 2) AS domain

Full query:

SELECT email, split_part(email, '@', 2) AS domain
FROM users;

Ref: http://www.postgresql.org/docs/current/static/functions-string.html

Credit to https://stackoverflow.com/a/19230892/1048433