Find email address that are part of a domain exclusion data extension

Question

I'm looking to find all email addresses in our database where the email address domain is included in one of our domain exclusion data extension.

I thought of using something like the below, but it isn't feasible given we have over 3k domains that are part of our domain exclusion data extension.

SELECT * FROM [mytable]
  WHERE Email_Address LIKE 'domain1'
  OR Email_Address LIKE 'domain2'
  OR Email_Address LIKE 'domain3'

Is this even possible in SFMC and if so, what supporting documentation exists?

Answer 1

You could accomplish something like this by joining the _subscribers data view with the domain exclusions table on the users email address domain in order to find matches between the two.

To identify a user's domain with SQL, you can use this snippet:

RIGHT(EmailAddress, LEN(EmailAddress) - CHARINDEX('@', EmailAddress))

The rest of the query should be as simple as an inner join.