Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39

    Unanswered: Another BETWEEN question

    Example
    Code:
    "SELECT item_name, rec_code FROM menu_item WHERE item_name BETWEEN 'A' AND 'G' ";
    AmI mistaken, but does this query exclude A and G in the results?

    And if so, should I overlap the subseguent grouping?

    ie

    BETWEEN A and G
    BETWEEN G and P
    BEWTEEN P and Z

    ??

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    BETWEEN is inclusive

    set your ranges for A-G, H-P, Q-Z

    rudy

  3. #3
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39
    More odd behavior...

    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?

    If I change 'Z' to 'Zz' i find Zebra soup. I thought MySQL was case-insensitive by default.
    Last edited by phpPete; 09-04-02 at 01:38.

  4. #4
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39
    Changing my ranges to:
    A - Gz
    H - Pz
    Q - Zz

    appears to solve my problem

    Last edited by phpPete; 09-04-02 at 08:05.

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Pete,

    Does it work if you do:
    BETWEEN 'A%' AND 'G%'
    Thanks,

    Matt

  6. #6
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39
    Hi Matt;

    No, you sugeestion doesn't work. When I change the lower bound to look to include a lowercase z that does the trick.

    I assume this works because the items that were omitted before are now within range. MySQL appears to grab the Upper bound as one would expect, but the lower bound needs to be set to the starting letter and the second letter set to z.

  7. #7
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Not sure, but the reason why I suggested % was to make it more like, well, LIKE. I am not sure BETWEEN 'A' AND 'X' is standard SQL, so all bets are off as to the behavior.
    Thanks,

    Matt

  8. #8
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39
    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

Posting Permissions

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