How to use domain exclusion DE via SQL

Question

I have de created out of domain exclusion template and de contains only one field 'domain', and have inconsistent values like 'gmail.com' '@hotmail.com' '%yahoo%'. I would like to use in SQL as suppression but could not find a way. I tried using in where clause Where emailaddress not in (select domain from exclusionde). I also tried using not exists. I tried WHERE RIGHT(EmailAddress, LEN(EmailAddress) - CHARINDEX('@', EmailAddress)) not in (select domain from exclusionde) but it's not working.

Any idea how to make it work via SQL?

Thanks.

Answer 1

For example, I have DE1:

SubscriberKey	EmailAddress
SubKey1	[email protected]
SubKey2	[email protected]
SubKey3	[email protected]
SubKey4	[email protected]

And excluded domain in the DE2:

Domain
@hotmail.com
yahoo.com
test.com

To extract records in DE1 that uses any excluded email domain, you can try something like this:

SELECT
a.SubscriberKey,
a.EmailAddress
FROM DE1 AS a
INNER JOIN DE2 AS b
ON a.EmailAddress LIKE CONCAT('%', b.Domain)

It will result into DE3:

SubscriberKey	EmailAddress
SubKey2	[email protected]
SubKey3	[email protected]
SubKey4	[email protected]

Try yourself: https://dbfiddle.uk/hrM27Jtk

Update: to get records not using the excluded domain, you can use the DE result above (let's call DE3) with left join:

SELECT
a.SubscriberKey,
a.EmailAddress
FROM DE1 AS a
LEFT JOIN DE3 AS b
ON a.SubscriberKey = b.SubscriberKey
WHERE
b.SubscriberKey IS NULL