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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Is the value of your particular COMMENT column null     Sometimes NOT LIKE doesn t know how to behave properly around nulls

[sql] SQL Not Like Statement not working

Examples related to sql

Examples related to where

Examples related to sql-like