Eldelshell Eldelshell - 1 year ago 89
SQL Question

Find emails that are present on a spammers table of domains

I have a table with user emails (Accounts) and another with a list of temporary email addresses domains (Spammers).

Accounts table

id | email
1 | test111122@example.com
2 | test1111223@example.com
3 | test@sharklasers.com

Spammers table

id | domain
1 | 0-mail.com
2 | 0815.ru
3 | sharklasers.com

Results after query

id | email
3 | test@sharklasers.com

How can I obtain all the emails on the Accounts table that are from Spammers?

I know there are many accepted email formats, but currently I'm only interested on the "standard" ones.


Answer Source

You can use a simple JOIN with a LIKE search:

select email from users u
join spammers s 
  on u.email like '%' || s.domain;

SQL Fiddle

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download