SQL LIKE statement results in unexpected behavior when field is delimited

Question

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?

Answer 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%'