如何从电子邮件地址中选择域名 [英] How to select domain name from email address

问题描述

我有类似[e-mail]，[e-mail] [e-mail]等的电子邮件地址. 我想要一个Mysql SELECT，它将修剪用户名和.com并以以下形式返回输出 gmail，ymail，hotmail等

I have email addresses like [e-mail], [e-mail] [e-mail] ... etc. I want a Mysql SELECT that will trim user names and .com and return output as gmail,ymail,hotmail, etc.

推荐答案

假定该域是单个单词域，例如gmail.com，yahoo.com，请使用

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

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

内部SUBSTR在@之后获得电子邮件地址的右侧，而外部SUBSTRING_INDEX将在第一个期间切断结果.

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.

否则，如果预计域包含多个词，例如mail.yahoo.com等，请使用:

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))将通过使用SUBSTRING_INDEX并从右到左计算的负计数来获得减去TLD (.com, .biz etc. part)的域的长度.

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.

这篇关于如何从电子邮件地址中选择域名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！