sql-server - 如何将电子邮件地址拆分为多个部分

Question

邮件数据:

Mailid
-------------------------------
venkattaramanan1985@gmail.com
madanraj@gmail.com

预期输出

Name                 DomainName  Extension
-------------------  ----------  ---------
venkattaramanan1985  gmail       com
madanraj             gmail       com

Answer 1

它有一个简单的一行解决方案，假设电子邮件地址是admin@system.org，下面的查询将返回如上所述的部分:

SELECT 
--admin
LEFT(emailAddres, CHARINDEX('@', emailAddres) - 1) AS accountName  

--system.org
RIGHT(emailAddres, LEN(emailAddres) - CHARINDEX('@', emailAddres)) AS domainWithExtension

--system
LEFT(RIGHT(emailAddres, LEN(emailAddres) - CHARINDEX('@', emailAddres)), CHARINDEX('.', RIGHT(emailAddres, LEN(emailAddres) - CHARINDEX('@', emailAddres))) - 1) AS domain

--org
RIGHT(RIGHT(emailAddres, LEN(emailAddres) - CHARINDEX('@', emailAddres)), LEN(RIGHT(emailAddres, LEN(emailAddres) - CHARINDEX('@', emailAddres))) - CHARINDEX('.', RIGHT(emailAddres, LEN(emailAddres) - CHARINDEX('@', emailAddres)))) AS extension

希望这有帮助。