SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Feb 2002
    Location
    Atlanta, GA
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    using BETWEEN misses a few records

    Sample queries:

    PHP Code:
    $query "SELECT item_name, rec_code FROM menu_item WHERE item_name BETWEEN 'A' AND 'G' ";
    $query "SELECT item_name, rec_code FROM menu_item WHERE item_name BETWEEN 'H' AND 'P' ";
    $query "SELECT item_name, rec_code FROM menu_item WHERE item_name BETWEEN 'Q' AND 'Z' "
    Sample item_name in DB

    Aardvark
    Ham Sandwich
    Palomino
    panda
    Rhino Pie
    Zebra soup

    using the above queries MySQL fails to return Zebra soup and also misses panda
    Any ideas why this might occur?
    Last edited by phpPete; Sep 3, 2002 at 20:24.


  2. #2
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    At a guess, it looks like the upper bound isn't considered. So "between 'H' and 'P'" will get anything starting with H, I, J, K, L, M, N, O.

    That could be wrong, but it's worth trying. Alter the second of those selects to "between 'H' and 'Q'" and see whether Panda comes back. If so, you'll know where the problem lies.

  3. #3
    SitePoint Addict
    Join Date
    Feb 2002
    Location
    Atlanta, GA
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK..this solves my problem:
    A - Gz
    H - Pz
    Q - Zz

    I mistakenly assumed BETWEEN was inclusive.


  4. #4
    SitePoint Addict
    Join Date
    Feb 2002
    Location
    Atlanta, GA
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK...the final solution is to dump BETWEEN and go this route:

    item_name >= 'A' and item_name < 'H' ...
    item_name >= 'H' and item_name < 'Q'...
    item_name >= 'Q' and item_name < CHAR(ORD('Z')+1)

    as recommended by Paul Dubois, author MySQL


  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    BETWEEN is inclusive

    i've seen this question before somewhere

    when you first presented it, it wasn't clear that the field being tested is bigger than 1 character

    if you're going to use BETWEEN but just test the first letter of a larger field, then you have to fudge the boundaries awkwardly --
    Code:
     where item_name between 'H' and 'Pzzzz'
    which theoretically will still let 'Pzzzzzz' slip through

    why not just use ordinary inequality tests --
    Code:
     where item_name >= 'H' and item_name < 'Q'
    when parsing BETWEEN, the database has to convert it anyway, using two "or equal" conditions, so why not take the bull by the horns and make the upper bound an inequality yourself

    that way you cover the alphabet cleanly

    note for mattr: BETWEEN is perfectly standard
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    whoa, i guess i wasn't fast enough

    that last one,
    Code:
    item_name >= 'Q' and item_name < CHAR(ORD('Z')+1)
    uses non-standard sql, so i would just say
    Code:
    item_name >= 'Q'
    which has the same effect

    well, not exactly the same effect, just as far as letters of the alphabet are concerned

    rudy

  7. #7
    SitePoint Addict
    Join Date
    Feb 2002
    Location
    Atlanta, GA
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    item_name >= 'Q' 
    That's nifty. I should have thought of that in the first place, so it's sort of a catch all for the last range.


  8. #8
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by r937
    note for mattr: BETWEEN is perfectly standard
    LOL, yes I know BETWEEN is, but is using it on VAR/CHAR data like that standard? I've been reading and I can't find anything on the topic of BETWEEN and single-char clauses.


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
  •