Query for selecting column from one table having text like column in another table

Question

There are two tables in PostgreSQL "Public domain" and "Emails" Public domain table has a list of domains in column1 e.g. abcd.com,xyz.com etc..

Table-"Public domain"
sr.no domains
1      yahoo.com
2      gmail.com
3      hotmail.com

Table-"Emails"
sr.no  emails
1      [email protected]
2      [email protected]
3      [email protected]

The query should return:
[email protected]
[email protected]

How can I achieve this?

Answer 1

You might use substring with position function as :

select e.emails
  from Emails as e
  join PublicDomain as p
    on ( substring(e.emails,position('@' in e.emails)+1,length(e.emails)) = p.domains );

emails
---------------
[email protected]
[email protected]