postgresql - PostgreSQL：匹配带有或不带有子域的电子邮件地址

Question

设想

在其大部分历史中，我的公司在电子邮件地址中使用子域，主要是按州划分，但其他公司有部门子域。我们所拥有的一些例子包括：

mo.widgits.com
sd.widgits.com
va.widgits.com
nhq.widgits.com
gis.widgits.com
tech.widgits.com

...等等。

新范式

几年前，高层管理人员决定他们希望我们都成为一个幸福的家庭。作为这种文化调整的一部分，他们将每个人的电子邮件地址更改为单一域，格式为[e-mail].

目前的挑战

在我们的许多公司数据库中，我们发现使用旧格式和新格式的混合记录。例如，同一个人可能[e-mail]在员工系统和[e-mail]培训系统中都有。我需要在各种系统中匹配个人，无论该系统中使用哪种格式的电子邮件。

所需的比赛

[e-mail] = [e-mail] -> true
[e-mail] = [e-mail] -> true
[e-mail] = [e-mail] -> false

如何做到这一点？

是否有一个正则表达式模式可用于匹配电子邮件地址，无论它们是哪种格式？或者我是否需要在尝试匹配之前手动提取子域？

标签： postgresqlpattern-matchingemail-address

Answer 1

在我的脑海中，您可以在比较所有电子邮件地址之前从所有电子邮件地址中删除子域（即仅比较电子邮件名称和域）。像这样的东西：

SELECT *
FROM emails
WHERE REGEXP_REPLACE(email1, '^(.*@).*?([^.]+\.[^.]+)$', '\1\2') =
      REGEXP_REPLACE(email2, '^(.*@).*?([^.]+\.[^.]+)$', '\1\2');

演示

数据：

WITH emails AS (
    SELECT '[e-mail]' AS email1, '[e-mail]' AS email2 UNION ALL
    SELECT '[e-mail]', '[e-mail]' UNION ALL
    SELECT '[e-mail]','[e-mail]'
)

以下是使用的正则表达式模式的解释：

^                   start of the email
    (.*@)           match email name including @ in \1
    .*?             consume content up, but not including
    ([^.]+\.[^.]+)  final domain only (e.g. google.com)
$                   end of the email

然后，我们替换\1\2为有效地删除任何子域组件。