SQL Not Like Statement not working

Question

I have the following code within a stored procedure.

WHERE
 WPP.ACCEPTED = 1 AND
 WPI.EMAIL LIKE '%@MATH.UCLA.EDU%' AND
 (WPP.SPEAKER = 0 OR
 WPP.SPEAKER IS NULL) AND
 WPP.COMMENT NOT LIKE '%CORE%' AND
 WPP.PROGRAMCODE = 'cmaws3'

The NOT LIKE statement is not working, and yes before anyone says anything there are items with the COMMENT column that does not include CORE and all the other columns are ok.

Does anyone know what is wrong with this?

Answer 1

If WPP.COMMENT contains NULL, the condition will not match.

This query:

SELECT  1
WHERE   NULL NOT LIKE '%test%'

will return nothing.

On a NULL column, both LIKE and NOT LIKE against any search string will return NULL.

Could you please post relevant values of a row which in your opinion should be returned but it isn't?

Answer 2

Is the value of your particular COMMENT column null?

Sometimes NOT LIKE doesn't know how to behave properly around nulls.

Answer 3

Do you receive an error or does it just give no result?

Answer 4

mattgcon,

Should work, do you get more rows if you run the same SQL with the "NOT LIKE" line commented out? If not, check the data. I know you mentioned in your question, but check that the actual SQL statement is using that clause. The other answers with NULL are also a good idea.