UPPER Question
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: UPPER Question

  1. #1
    Join Date
    Jun 2001
    Posts
    243

    Question

    Which SELECT statement will display the style, color, and lot_no for all cars based on model?

    A. Select style, color, lot_no
    From auto
    Where model = upper('&model');

    B. Select style, color, lot_no
    From auto
    Where model = '&model';

    Thank you.

  2. #2
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    Both queries will return the same thing only difference is, with the first query your passed value will be converted into UPPER CASE.

    Thanks

  3. #3
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Of course, it does depend on how the data stored in the table, if the data stored in the MODEL column is in UPPERCASE, then a query you specify in lowercase, will not return any rows. The UPPER function forces the passed variable to be made upper which.

    I.e if the data in the table is

    MODEL

    SUBARU

    A. Select style, color, lot_no
    From auto
    Where model = upper('subaru');

    B. Select style, color, lot_no
    From auto
    Where model = 'subaru';

    Query a, will return 1 row and query b, will return no rows returned.

    Cheers
    OCP 8i, 9i DBA
    Brisbane Australia

  4. #4
    Join Date
    Aug 2001
    Posts
    8
    You can display the style, color, and lot_no for all cars based on model if you use :

    Select style, color, lot_no
    From auto
    Where upper(model) = upper('&model');

    Regards,

    Sonal.

  5. #5
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    if i am not wrong...u shld always matching the query content with UPPERCASE as
    oracle always return CHAR,STRING in caps and therefore...u shld match it in
    uppercase.

    ngwh,
    Singapore.

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    This question reminds me of one of the questions in the PL/SQL certification exam.

    I'm not sure about Oracle return data back in CAPS. I think it returns it back however it is stored in the database. lower, upper, initcap or otherwse.

    [Edited by grjohnson on 08-23-2001 at 10:35 PM]
    OCP 8i, 9i DBA
    Brisbane Australia

  7. #7
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    data is returned exactly as it was stored in the DB.
    as Sonal said, the only way to get rid of case problems is to use upper(a) = upper(b) [or lower of course]

  8. #8
    Join Date
    Jun 2001
    Posts
    316
    but when using upper....
    I faced this problem earlier..
    if u have a laarge table..then it would be a problem as the indexes are not used...but a full table scan...and my select query for the worst sql xecuted happens to be mine
    So for that u would have to force indexes......

  9. #9
    Join Date
    Mar 2001
    Posts
    188
    Hey pipo,
    can you detail what you mean with
    the only way to get rid of case problems is to use upper(a) = upper(b) [or lower of course]
    All others here are clear without what you mean.

    Thanks very much
    Best Regards
    Thomas Schmidt

    Thomas_Schmidt@eplus-online.de
    If you have no aims, you will never reach a goal !!!

  10. #10
    Join Date
    Jun 2001
    Posts
    316
    i liked db2 istead of oracle for this purpose

    db2 has somthing like translate(column_name) like '%me%' and it does it for me for all cases....
    and i assume we dont have such a thing in oracle...do we?

    thanx

Posting Permissions

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


Click Here to Expand Forum to Full Width