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,
emailslist looks like this:
[email protected]; [email protected]
[email protected]; [email protected]
[email protected]; [email protected]
the desired output should look like this:
[email protected]; [email protected]
[email protected]; [email protected] (this row should be returned because it also contains '@outlook.com' which isn't on my exclude list)
CodePudding user response：
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
('[email protected]; [email protected]'),
('[email protected]; [email protected]'),
('[email protected];'),
('[email protected]; [email protected]');
gives:
| COLUMN1 | CLEANED | LOGIC |
|---|---|---|
| [email protected]; [email protected] | [email protected]; [email protected] | TRUE |
| [email protected]; [email protected] | [email protected]; abc | TRUE |
| [email protected]; | 123; | FALSE |
| [email protected]; [email protected] | 123; abc | FALSE |
which can be merged into one line:
,REGEXP_LIKE(REGEXP_REPLACE(column1, '@((icloud)|(mac))\\.com'), '.*@.*\\.com.*') as logic
CodePudding user response：
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 '%@%'