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

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

Thanks

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

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.

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.

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]

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] ;)

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:p
So for that u would have to force indexes......

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

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

Well you have to use
upper(a) = upper(b)

In case your query is on a large table then use fuunction based indexes (which is new in Oracle8i).

thanks for help sudip
now i have understand what pipo want to say

So long

Quote:

---

Originally posted by 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

---

No, but SQLServer does it even better. They allow you to create the entire database is case-insensitive mode. This means that 'me' = 'ME'. No need for function-based indexes. The fact that Oracle cannot do this is unforgivable, IMHO.

- Chris

I disagree. sql server is a PC (or Access) based product.
it means never case-sensetive.

yes, good side is you always can match without case problem. but on the other side, can you retrive data lower case only, upper case only, or Mixed case? - no way.
DB2, and Oracle give you function support those options,
Sql server gives you no solution, no flexibilty.

Quote:

---

Originally posted by jm
I disagree. sql server is a PC (or Access) based product.
it means never case-sensetive.

yes, good side is you always can match without case problem. but on the other side, can you retrive data lower case only, upper case only, or Mixed case? - no way.
DB2, and Oracle give you function support those options,
Sql server gives you no solution, no flexibilty.

---

Sorry, but you are incorrect. It is irrelevant whether the file system is case-sensitive or not. If such were the case, that would mean that Word would be case-insensitive as well.

Further, even if you create a database as case-insensitive, you can still store data in whatever case you like, including mixed case. the case-insensitive option simply changes the code-page that is used to interpret the characters. This means that the sort order and all comparisons will be case-insensitive. Again, this is separate from how the data is stored.

If you don't desire this functionality, you don't have to have it. The default in SQLServer has always been to create a case-sensitive database, which works just like Oracle. However, since I started using SQLServer (4.x), I have created all my databases as case-insensitive. In this way, I could have the user enter their data in mixed case, enter their searches in mixed case, and still have the benefit of indexed queries. When I converted to Oracle (7.x) and found no such support, I was more than a little dissappointed. I was horrified. My only choice was to duplicate every string field, trigger-fill the new columns with uppercase values and put the indexes on the new columns. Needless to say, I was not pleased with this alternative, as it did bad things to my cache utilization.

Finally, in 8i, we have function-based indexes. While this does finally solve the basic problem, it is definitely a less elegant solution. IMHO, it would be much better to provide a case-insensitive database option such as SQLServer's.

But that's just my opinion, I could be wrong ;)

- Chris