Search code examples
postgresqlpattern-matchingemail-address

PostgreSQL: Match Email Addresses With or Without Subdomains

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?

Solution

  • 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');

    screen capture from demo link below

    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.