Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Case insensitivity

Re: Case insensitivity

From: John P Weatherman <asahoshi_at_infionline.net>
Date: Wed, 21 Apr 2004 09:06:41 -0400
Message-ID: <410-22004432113641730@infionline.net>

This will work if the problem is that the table data is mixed case, but if the problem is that the search criteria is freeform entered and there is no way to force it to single case, I am not sure there is a way to do this. Is there a way to force query rewrite to use a rewrite you specify to make the passed value into an upper/lower? Which side of the search has mixed case?

Regards,

John P Weatherman
Oracle DBA
Advance America

> [Original Message]
> From: Nuno Souto <dbvision_at_optusnet.com.au>
> To: <oracle-l_at_freelists.org>
> Date: 4/22/2004 1:53:31 AM
> Subject: Re: Case insensitivity
>
> ----- 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
> -----------------------------------------------------------------



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 - 08:04:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US