use ILIKE ANY on values from a column in a different table

Question

Have a list of email domains and want to tag 'free' if the domain is like a free domain, this works for me with a hard coded list like this:

CASE WHEN lower(email_domain) ilike any ('%%gmail%%','%%yahoo%%', '%%hotmail%%') THEN 'free' else 'business' end

but what I really want to do is get the list of free domains from a column in a diffrent table which looks something like this:

| SUBSTRING_VALUE   |
|:------------------|
| "gmail"           |
| "hotmail"         |
| "yahoo"           |

I've tried using a subquery with listagg or arrayagg, but it does not work, any way of doing this?

Answer 1

Using JOIN:

SELECT DISTINCT t.*, 
  CASE WHEN f.domain IS NOT NULL THEN 'free' ELSE 'business' END AS email_type
FROM test t
LEFT JOIN freedomain f
  ON t.email_domain ILIKE CONCAT('%', f.domain, '%'); 

Sidenote: When ILIKE is used, there is no need to LOWER the email_domain column as comparison is already case-insensitive.

---

Sample:

CREATE OR REPLACE TABLE freedomain
AS
SELECT 'gmail' AS domain 
UNION SELECT 'yahoo'
UNION SELECT 'hotmail';

CREATE OR REPLACE TABLE test
AS
SELECT 'biz' AS email_domain
UNION SELECT 'hotmail';

Output:

EMAIL_DOMAIN    EMAIL_TYPE
hotmail         free
biz             business