I'll say that using LIKE or NOT LIKE works great when splitting out emails. I usally do '%@%', but something strange is going on here...
I have a field and it has delimited values for example:
Do not Mass Email;Team Specific Restriction Do not Contact (ALL);Do not Mass Email Do not Mass Email Do not Mass Email;Do not Send Fundraising emails
Those are some of the possible values of this field. My query looks like:
SELECT * FROM [All Donors no PK] WHERE Contact_Restrictions__pc NOT LIKE 'Do not Mass Email'
I've even tried:
SELECT * FROM [All Donors no PK] WHERE Contact_Restrictions__pc NOT LIKE '%Do not Mass Email%'
Which seems worse.
Here's what is happening. It's stripping out too many results. My source table has 32,000 rows. I exported it and put it into excel and removed all of the columns that have any form of "Do not Mass Email" and that resulted in somewhere around 24,000 records. So manual filtering in excel gives me around 24,000, but my NOT LIKE statement gives me closer to 1500. I'm not sure what's going on because even with fuzzy matching off, it's still not getting anywhere close to the right number.
Oddly enough, this query
SELECT Account_ID FROM [All Donors no PK] WHERE Contact_Restrictions__pc LIKE '%Do not Mass Email%'
Seems to be spot on and gives me an idea for a workaround. but still, I'd like to know what I'm missing with the NOT LIKE. Perhaps I don't fully understand the behavior?