UPPER Question

[gaegoori]

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.

[irehman]

Both queries will return the same thing only difference is, with the first query your passed value will be converted into UPPER CASE.

Thanks

[grjohnson]

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

[Sonal1]

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.

[ngwh]

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.

[grjohnson]

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]

[pipo]

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]

[helpme]

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......

[tomate]

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

[helpme]

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