Menu
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.
877
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.
A domain name (often simply called a domain) is an easy-to-remember name that's associated with a physical IP address on the Internet. It's the unique name that appears after the @ sign in email addresses, and after www. in web addresses.
To extract domain from email address Select a blank cell to place this formula =RIGHT(A2,LEN(A2)-FIND("@",A2)), press Enter key, and drag fill handle down to the cells which need this formula.
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.
165
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.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
