UPPER Question

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