SQL Not Like Statement not working

sql where sql-like
mattgcon picture mattgcon · Oct 28, 2009 · Viewed 202.4k times · Source

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

Quassnoi picture Quassnoi · Oct 28, 2009

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?

Related questions

How to use NULL or empty string in SQL

I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.

Read More
Oracle SQL : timestamps in where clause

I need to look up rows within a particular time frame. select * from TableA where startdate >= '12-01-2012 21:24:00' and startdate <= '12-01-2012 21:25:33' I.e.: I need to look up rows with timestamp precision of …

Read More
querying WHERE condition to character length?

I have a database with a large number of words but i want to select only those records where the character length is equal to a given number (in example case 3): $query = ("SELECT * FROM $db WHERE conditions AND length = 3"); But …

Read More