选择email地址(SQL)

Question

我在一个表中有一个列(列名是" from ")，看起来像这样

[e-mail]
frank ocean real [e-mail]
[e-mail]
frits west [e-mail]

我想只选择电子邮件地址，我该怎么做?用子字符串?我可以找到一个域名，但我想要完整的邮件地址，像这样:

[e-mail]
[e-mail]
[e-mail]
[e-mail]

谢谢!

Answer 1

将字符串反转并在地址前查找空格:try this

CREATE TABLE #Addresses (EmailAddress VARCHAR(100))
INSERT INTO #Addresses (EmailAddress)
SELECT '[e-mail]'
UNION
SELECT 'frank ocean real [e-mail]'
UNION
SELECT '[e-mail]'
UNION
SELECT 'frits west [e-mail]'
SELECT LTRIM(RTRIM(RIGHT(EmailAddress, CHARINDEX(' ', REVERSE(' ' + EmailAddress),CHARINDEX('@', REVERSE(' '+emailAddress)))))) FROM #Addresses

编辑:如果您有任何包含地址后面的名称的字符串，您可以使用以下命令来删除地址:

CREATE TABLE #Addresses (EmailAddress VARCHAR(100))
INSERT INTO #Addresses (EmailAddress)
SELECT '[e-mail]'
UNION
SELECT 'frank ocean real [e-mail]'
UNION
SELECT '[e-mail]'
UNION
SELECT 'frits west [e-mail]'
UNION 
SELECT '[e-mail] my name'
SELECT LTRIM(RTRIM(LEFT(RIGHT(EmailAddress, CHARINDEX(' ', REVERSE(' ' + EmailAddress),CHARINDEX('@', REVERSE(' '+emailAddress)))), CHARINDEX(' ', EmailAddress + ' '))) FROM #Addresses

DROP TABLE #Addresses

编辑2:忘记添加修剪功能

编辑3:使用OP的列名(未发布的表名)的最终代码:

SELECT LTRIM(RTRIM(LEFT(RIGHT([From], CHARINDEX(' ', REVERSE(' ' + [From]),CHARINDEX('@', REVERSE(' '+[From])))), CHARINDEX(' ', [From]+ ' '))) FROM -- whatever your table is named

Answer 2

如果输出始终是您给出的格式，那么希望这将工作:

SELECT RIGHT([From], CHARINDEX(' ', REVERSE(' ' + [From])) - 1) AS [Result]
FROM   YourTable 

这只会在你想要的输入(实际的电子邮件地址)之前有一个空格时才会起作用。我将此用于类似的目的，用于处理一些遗留的可疑客户数据。

这是SQL Server，我不知道它是否会工作在任何其他RDBMS