Where clause to exclude specific email domains

user_10992922 0
Viewed 122

I have a list of emails and I want to write a where statement, to exclude rows that only contain the email domains %@icloud.com or %@mac.com

For example, emails list looks like this:

abc@gmail.com; 123@hotmail.com

123@outlook.com; abc@icloud.com

123@icloud.com;

123@icloud.com; abc@mac.com

the desired output should look like this:

abc@gmail.com; 123@hotmail.com

123@outlook.com; abc@icloud.com (this row should be returned because it also contains '@outlook.com' which isn't on my exclude list)

snowflake-cloud-data-platform sql
3 Answers

Given negative lookaheads are not supported, away to achieve that is two remove the unwanted matched, and then look for an "any email left" match:

SELECT column1
    ,REGEXP_REPLACE(column1, '@((icloud)|(mac))\\.com', '') as cleaned
    ,REGEXP_LIKE(cleaned, '.*@.*\\.com.*') as logic
FROM VALUES
('abc@gmail.com; 123@hotmail.com'),
('123@outlook.com; abc@icloud.com'),
('123@icloud.com;'),
('123@icloud.com; abc@mac.com');

gives:

COLUMN1 CLEANED LOGIC
abc@gmail.com; 123@hotmail.com abc@gmail.com; 123@hotmail.com TRUE
123@outlook.com; abc@icloud.com 123@outlook.com; abc TRUE
123@icloud.com; 123; FALSE
123@icloud.com; abc@mac.com 123; abc FALSE

which can be merged into one line:

,REGEXP_LIKE(REGEXP_REPLACE(column1, '@((icloud)|(mac))\\.com'), '.*@.*\\.com.*') as logic
user_439920

If you prefer a more vanilla approach to Simeon's solution

where replace(replace(col,'@icloud.com',''), '@mac.com','') like '%@%'

In Snowflake, the replacement string is optional, which shortens that to

where replace(replace(col,'@icloud.com'), '@mac.com') like '%@%'
user_99471590

This is based on string split approach in SQL server, using split_to_table function, you probably have to tweak the syntax a little:

select *
from t
where exists (
    select *
    from split_to_table(t.emails, ';') as sv
    where sv.value not like '%@icloud.com'
    and sv.value not like '%@mac.com'
)
user_870150
Related