From oracle-l-bounce@freelists.org Wed Apr 21 08:04:41 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3LD48v32545 for ; Wed, 21 Apr 2004 08:04:21 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i3LD3s632493 for ; Wed, 21 Apr 2004 08:04:07 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4C97172CD94; Wed, 21 Apr 2004 07:56:26 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 23047-30; Wed, 21 Apr 2004 07:56:25 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 425DE72CF16; Wed, 21 Apr 2004 07:56:24 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 21 Apr 2004 07:55:10 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7D17172CC91 for ; Wed, 21 Apr 2004 07:55:09 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 22505-39 for ; Wed, 21 Apr 2004 07:55:09 -0500 (EST) Received: from starling.mail.pas.earthlink.net (starling.mail.pas.earthlink.net [207.217.120.227]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C55F672CDCE for ; Wed, 21 Apr 2004 07:55:08 -0500 (EST) Received: from user-2ivfc9q.dialup.mindspring.com ([165.247.177.58] helo=infionline.net) by starling.mail.pas.earthlink.net with asmtp (Exim 3.36 #4) id 1BGHR4-0001yj-00 for oracle-l@freelists.org; Wed, 21 Apr 2004 06:06:43 -0700 Message-ID: <410-22004432113641730@infionline.net> X-Priority: 3 X-Mailer: EarthLink MailBox 2004.1.89.0 (Windows) From: "John P Weatherman" To: oracle-l@freelists.org Subject: Re: Case insensitivity Date: Wed, 21 Apr 2004 09:06:41 -0400 MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII X-ELNK-Trace: b4d21b641634dbbe40ea0adc697db4f96818e31467099fbadee3698e4f1644e3a43b829c6bd952d9350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 3432 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: asahoshi@infionline.net Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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 > To: > Date: 4/22/2004 1:53:31 AM > Subject: Re: Case insensitivity > > ----- Original Message ----- > From: "Skurský Michal" > > 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@optusnet.com.au > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@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@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 -----------------------------------------------------------------