1

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?

2
  • 1
    It might be due to null values in this field. Null will not be matched in like or not like. Try adding OR Contact_Restrictions__pc IS NULL to your WHERE statement. This is due to SQL 3 Value Logic: gortonington.com/tag/three-valued-logic Jun 7 at 23:19
  • Thanks, I think I tried that, but I'll give it a go just in case. Tried so many things are started to lose track.
    – garek007
    Jun 8 at 2:58
1

This isn't really the forum for discussing SQL, unless there's some specific application of SQL relating to Marketing Cloud that only we can help with.

NULL doesn't equal NULL or anything else, "LIKE" or "NOT LIKE" included. NULL's not possible to evaluate. "LIKE '%Do not Mass Email%'" matches some stuff - the stuff you're interested in. "NOT LIKE '%Do not Mass Email%'" works for non-NULLS - the records with values. For NULLS, they don't match anything. They're nothing and impossible to evaluate. Take a look at COALESCE(). It's SQL.

SELECT *
FROM [All Donors no PK]
WHERE COALESCE(Contact_Restrictions__pc,'') NOT LIKE '%Do not Mass Email%'
2
  • This makes sense because there are a lot of null values. Thanks!
    – garek007
    Jun 8 at 15:07
  • 1
    This worked great BTW!
    – garek007
    Jun 9 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.