How to select domain name from email address

Question

I have a email address like [email protected] and [email protected] [email protected] ... etc I want a Mysql select query so that it would trim user names and .com an returns output as gmail,ymail,hotmail etc

Answer 1

Try this:

select SUBSTR(field_name, INSTR(field_name, '@'), INSTR(field_name, '.'))

Answer 2

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 .com, .biz part (not sure the exact term) by using SUBSTRING_INDEX with a negative count which will calculate from right to left.