I have email addresses like [email protected]
, [email protected] [email protected]
... etc.
I want a Mysql SELECT
that will trim user names and .com and return output as
gmail
,ymail
,hotmail
, etc.
I have email addresses like [email protected]
, [email protected] [email protected]
... etc.
I want a Mysql SELECT
that will trim user names and .com and return output as
gmail
,ymail
,hotmail
, etc.
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.