PostgreSQL: Match Email Addresses With or Without Subdomains

Question

Scenario

For most of its history, my company used subdomains in the email addresses, mostly by state, but others had division subdomains. A few examples of what we had include:

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

...and so on.

New Paradigm

A few years ago, top management decided that they wanted us all to be one happy family; as part of this cultural realignment, they changed everyone's email addresses to the single domain, in the format of [email protected].

Present Challenges

In many of our corporate databases, we find a mixture of records using either the old format and the new format. For example, the same individual might have [email protected] in the employee system, and [email protected] in the training system. I have a need to match individuals up among the various systems regardless of which format email is used for them in that system.

Desired Matches

[email protected] = [email protected] -> true
[email protected] = [email protected] -> true
[email protected] = [email protected] -> false

How to Accomplish This?

Is there a regex pattern that I can use to match email addresses regardless of which format they are? Or will I need to manually extract out the subdomain before attempting to match them?

Answer 1

Off the top of my head, you could strip off the subdomain from all email addresses before comparing them (that is, compare only the email name and domain). Something like this:

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

Demo

Data:

WITH emails AS (
    SELECT '[email protected]' AS email1, '[email protected]' AS email2 UNION ALL
    SELECT '[email protected]', '[email protected]' UNION ALL
    SELECT '[email protected]','[email protected]'
)

Here is an explanation of the regex pattern used:

^                   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

Then, we replace with \1\2 to effectively remove any subdomain components.

Answer 2

How about something like this?

SELECT 
  * 
FROM 
  (
    SELECT 
      table1.email, 
      table2.email, 
      SPLIT_PART(table1.email, '@', 1) AS table1_username, 
      SPLIT_PART(table2.email, '@', 1) AS table2_username, 
      SPLIT_PART(table1.email, '@', 2) AS table1_domain, 
      SPLIT_PART(table2.email, '@', 2) AS table2_domain 
    FROM 
      table1 CROSS 
      JOIN table2
  ) S 
WHERE 
  (
    table1_username = table2_username 
    AND (
      table1_domain like '%.' || table2_domain 
      OR table2_domain like '%.' || table1_domain
    )
  );