Re: Case insensitivity

From: Nuno Souto <dbvision_at_optusnet.com.au>
Date: Wed, 21 Apr 2004 19:53:30 +1000
Message-ID: <013101c42786$87f974f0$9b00a8c0@dcs001>

• Original Message ----- From: "Skurský Michal" <Michal.Skursky_at_pvt.cz>
  > I have an application in which I cannot change the code. There is a =
  > search function in the application on char or varchar2 table column. =
  > Search function is case sensitive as the author didn't use lower or =
  > upper functions in select statement.
  > My question is:
  > Does exist any trick, how to make this search function case insensitive =
  > only through database or user parameters/options? - I cannot imagine =
  > that, but ....=20
  > Oracle 8.1.7, Win2000
  > The reason why I am asking this question too is, that I was told, that =
  > MS SQL has such an option.
  

It might help if you use a later version than a 6 year old release... With 8i you can do this:
Rename the table to something else and slap a view with the column in question with an "upper()" around it. Then create a function-based index with the same function.
>From 9i you can ALSO do this:

If the table is updated as well, then you need an "INSTEAD OF" trigger to make the view update like the table. And Bob as they say, is your uncle.

Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_optusnet.com.au

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Apr 21 2004 - 04:50:46 CDT