如何匹配两个包含友好电子邮件地址的电子邮件字段

One table has "John Doe <jdoe@aol.com>" while another has "jdoe@aol.com". Is there a UDF or alternative method that'll match the email address from the first field against the second field?

一张桌子有“John Doe ”,另一张桌子有“jdoe@aol.com”。是否有一个UDF或替代方法将第一个字段中的电子邮件地址与第二个字段匹配? @aol.com>

This isn't going to be production code, I just need it for running an ad-hoc analysis. It's a shame the DB doesn't store both friendly and non-friendly email addresses.

这不是生产代码,我只需要它来运行临时分析。遗憾的是,DB不存储友好和非友好的电子邮件地址。

Update: Fixed the formatting, should be < and > on the first one.

更新:修复了格式,第一个应该是 <和> 。

4 个解决方案

#1

You could do a join using LOCATE method, something like...

你可以使用LOCATE方法进行连接,比如...

 SELECT * FROM table1 JOIN table2 ON (LOCATE(table2.real_email, table1.friend_email) > 0)

#2

I would split the email addresses on the last space- this should give you the email address. The exact code would depend on your database, but some rough pseudocode:

我会在最后一个空格中拆分电子邮件地址 - 这应该会给你电子邮件地址。确切的代码取决于您的数据库,但一些粗略的伪代码:

email = row.email
parts = email.split(" ")
real_email = parts[ len(parts) - 1 ]

#3

You should be able to use the LIKE keyword depending on how consistent the pattern is for the "friendly" email addresses.

您应该能够使用LIKE关键字,具体取决于模式与“友好”电子邮件地址的一致性。

SELECT
     T1.nonfriendly_email_address,
     T2.friendly_email_address
FROM
     My_Table T1
INNER JOIN My_Table T2 ON
     T2.friendly_email_address LIKE '%<' + T1.nonfriendly_email_address + '>'

#4

Perhaps the following TSQL code can help you:

也许以下TSQL代码可以帮助您:

DECLARE @email varchar(200)
SELECT @email = 'John Doe jdoe@aol.com'

SELECT REVERSE(SUBSTRING(REVERSE(@email), 0,CHARINDEX(' ', REVERSE(@email))))

That statement returns:

该声明返回:

jdoe@aol.com

Talking through the logic:

通过逻辑说话:

Reverse the email column

撤消电子邮件列

Find the index of the first ' ' character ... everything up to this point is your actual email address

找到第一个''字符的索引...到目前为止的所有内容都是您的实际电子邮件地址

Substring the column, from the start of the (reversed) string, to the index found at step 2.

从(反向)字符串的开头将列子行化为在步骤2中找到的索引。

Reverse the string again, putting it in the right order.

再次反转字符串,按正确的顺序排列。

There might be more elegant ways of doing it, but that would work, and you could therefore use it for one side of your JOIN. It works because email addresses cannot contain spaces, so therefore the last space (or first when you reverse it) will be the separator between your actual email address, and friendly one. As far as I know TSQL does not contain a LastIndexOf() function, which would have been handy to avoid the double Reverse() function calls.

可能有更优雅的方式,但这可以工作,因此您可以将它用于JOIN的一侧。它的工作原理是因为电子邮件地址不能包含空格,因此最后一个空格(或者当你将其反转时)将是实际电子邮件地址和友好电子邮件地址之间的分隔符。据我所知,TSQL不包含LastIndexOf()函数,这样可以避免双Reverse()函数调用。

#1