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 firstname.lastname@widgits.com.

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 porky.pig@widgits.com in the employee system, and porky.pig@in.widgits.com 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

porky.pig@in.widgits.com = porky.pig@widgits.com -> true
mary.poppins@widgits.com = mary.poppins@nhq.widgits.com -> true
bob.baker@widgits.com = bob.barker@gis.widgits.com -> 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 'porky.pig@in.widgits.com' AS email1, 'porky.pig@widgits.com' AS email2 UNION ALL
    SELECT 'mary.poppins@widgits.com', 'mary.poppins@nhq.widgits.com' UNION ALL
    SELECT 'bob.baker@widgits.com','bob.barker@gis.widgits.com'
)

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.