From oracle-l-bounce@freelists.org Wed Apr 21 04:50:46 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3L9oVN30808 for ; Wed, 21 Apr 2004 04:50:41 -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 i3L9oL630780 for ; Wed, 21 Apr 2004 04:50:31 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DDB6C72CBEF; Wed, 21 Apr 2004 04:43:24 -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 04909-47; Wed, 21 Apr 2004 04:43:24 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2C26672CBE1; Wed, 21 Apr 2004 04:43:18 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 21 Apr 2004 04:42:01 -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 B8B6572C986 for ; Wed, 21 Apr 2004 04:42:00 -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 04565-61 for ; Wed, 21 Apr 2004 04:42:00 -0500 (EST) Received: from mail019.syd.optusnet.com.au (mail019.syd.optusnet.com.au [211.29.132.73]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 558E372CAAC for ; Wed, 21 Apr 2004 04:41:58 -0500 (EST) Received: from dcs001 (c211-30-91-186.belrs3.nsw.optusnet.com.au [211.30.91.186]) by mail019.syd.optusnet.com.au (8.11.6p2/8.11.6) with SMTP id i3L9rUB24523 for ; Wed, 21 Apr 2004 19:53:31 +1000 Message-ID: <013101c42786$87f974f0$9b00a8c0@dcs001> From: "Nuno Souto" To: References: <8D6175338BE782478D2A7035315851C603253E@brnw00.pvt.cz> Subject: Re: Case insensitivity Date: Wed, 21 Apr 2004 19:53:30 +1000 MIME-Version: 1.0 Content-type: text/plain; charset=Windows-1252 Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3426 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: dbvision@optusnet.com.au Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org ----- 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 -----------------------------------------------------------------