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?

What do you mean by "not working"? Throwing an exception? Or is just always evaluated as false? — James Cronen, Oct 28 '09 at 16:53
Could you please post relevant values of a row which in your opinion should be returned but it isn't? — Quassnoi, Oct 28 '09 at 17:02
@mattgcon if you get an answer that answers the question you've asked, please "accept" the answer by clicking the check below the number of votes. — David Oneill, Oct 28 '09 at 17:02
Tenner - thank you very much, its the little things like that get me frustrated, so easy right there in your face stuff. But thank you that worked great. — mattgcon, Oct 28 '09 at 17:12
Raj - I was actually unaware that I was supposed to click on the check mark. That is how I mark the answer that worked right? — mattgcon, Oct 28 '09 at 17:17
`@mattgcon`: it's updated in a cron job. It will take a while before it updates but it's completely worth it. — Quassnoi, Oct 28 '09 at 17:32
oh ok, thank you everyone. This is the best site that I have found for help. I hope some day I will be able to answer some questions for other people someday — mattgcon, Oct 28 '09 at 17:34

score 66 · Accepted Answer · answered Oct 28 '09 at 16:55

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?

score 15 · Answer 2 · edited Jan 31 '14 at 12:54

Just come across this, the answer is simple, use ISNULL. SQL won't return rows if the field you are testing has no value (in some of the records) when doing a text comparison search, eg:

WHERE wpp.comment NOT LIKE '%CORE%'

So, you have temporarily substitute a value in the null (empty) records by using the ISNULL command, eg

WHERE (ISNULL(wpp.comment,'')) NOT LIKE '%CORE%'

This will then show all your records that have nulls and omit any that have your matching criteria. If you wanted, you could put something in the commas to help you remember, eg

WHERE (ISNULL(wpp.comment,'some_records_have_no_value')) NOT LIKE '%CORE%'

score 7 · Answer 3 · answered Oct 28 '09 at 16:56

7

Is the value of your particular COMMENT column null?

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

answered Oct 28 '09 at 16:56

James Cronen

5,715
2
32
52

score 6 · Answer 4 · answered Apr 03 '12 at 14:24

6

I just came across the same issue, and solved it, but not before I found this post. And seeing as your question wasn't really answered, here's my solution (which will hopefully work for you, or anyone else searching for the same thing I did;

Instead of;

... AND WPP.COMMENT NOT LIKE '%CORE%' ...

Try;

... AND NOT WPP.COMMENT LIKE '%CORE%' ...

Basically moving the "NOT" the other side of the field I was evaluating worked for me.

answered Apr 03 '12 at 14:24

Valiante

182
2
1

3

Welcome to stackoverflow. Actually that still would not include nulls, which is the correct behavior because `null` is not equal to anything, not even itself. If you want to include the nulls, either add a condition ie `OR Column IS NULL` or use `COALESCE` so the value is never null. – Leigh Apr 04 '12 at 01:52
This doesn't work with NULL values...the answer that worked is the one below this. – MattE Jul 16 '19 at 17:55

score 1 · Answer 5 · answered Oct 28 '09 at 16:57

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.

SQL Not Like Statement not working

5 Answers5

Linked