SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql "like" and "not like"

    Hi

    I have a problem in my search, I search in a table for "like ..." and "not like ..." and I still end up with less than all the rows...

    Here it is:

    SELECT count(*) FROM cases WHERE username like "karops" and completed>="2001-01-01" and completed<="2001-12-31"

    result: 264 hits

    SELECT count(*) FROM cases WHERE username like "karops" and completed>="2001-01-01" and completed<="2001-12-31" and finished like "yes"

    result: 212 hits

    The final search should result in (264-212) hits (52):

    SELECT count(*) FROM cases WHERE username like "karops" and completed>="2001-01-01" and completed<="2001-12-31" and finished not like "yes"

    But it only results in 38 hits...

    I have tried exchanging like and not like with == and != ... Same result

    Is this a problem with mysql, the search or what?

  2. #2
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hello,

    I just want to warn you about your like syntax

    if you use
    ------
    finished like "yes"
    ------

    it is the same as
    -------
    finsihed = 'yes'
    -------

    Are you sure you wanna do this in more expensive way by using like?
    or you wanna do
    ----------
    finished like "%yes%"
    ---------

    Correct that part and try again. Lets see what you will get.

    IMHO

  3. #3
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Sorry, didn't help... Same result.

    (and usually I use %, but I didn't think it was necessary here...)

    Extra information:

    In mysql finished is enum ('yes','no','y','n') default NULL

    and the missing rows all have '' (nothing) in finished, but there are some rows that have '' that I can find.

  4. #4
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LIKE and NOT LIKE can't retrieve NULL values, because a NULL record is neither LIKE nor NOT LIKE anything - it's just NULL.

    NULLs are a bit strange to get your head around, but it does make sense once you understand them. NULL is not the same as zero or blank; NULL means "unknown or unquantifiable value". NULL isn't equal to or like anything; in fact, NULL is not even equal to NULL!

    With your query, when the database gets to a record with a NULL value in the finished field, it can't say that NULL is like 'yes' (because it doesn't know what NULL is exactly; it's an unknown value), and in the next query it can't say that NULL is not like 'yes' (again because it doesn't know what NULL is exactly; it's an unknown value).

    The only way to test for NULLS is by using "IS NULL" or "IS NOT NULL". If you ran the query:

    SELECT count(*) FROM cases WHERE username like "karops" and completed>="2001-01-01" and completed<="2001-12-31" and finished IS NULL

    you'd probably find your missing 14 records. If you wanted to combine them in the "not like" query, you could write it as:

    SELECT count(*) FROM cases WHERE username like "karops" and completed>="2001-01-01" and completed<="2001-12-31" and (finished NOT LIKE 'yes' OR finished IS NULL)

    which should get you your 52 records.

    (Note that you say there are "some records with '' that you can find" - I imagine these will have blank entries rather than NULL values, which means the NOT LIKE expression will pick them up as it can compare blank to 'yes').

    Hope that helps, I don't know if I explained NULLs very well. If you need to read more to understand it, try www.mysql.com - there ought to be a decent explanation of NULLs there.

    Hope that helps .

  5. #5
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You explained it perfectly

    And I found the missing 14!

    Thanks to everybody for their help.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •