Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 34645 invoked from network); 24 Jun 2008 08:32:02 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 24 Jun 2008 08:32:02 -0000 Received: (qmail 64673 invoked by uid 500); 24 Jun 2008 08:32:02 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 64652 invoked by uid 500); 24 Jun 2008 08:32:02 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 64641 invoked by uid 99); 24 Jun 2008 08:32:02 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 24 Jun 2008 01:32:02 -0700 X-ASF-Spam-Status: No, hits=-1.0 required=10.0 tests=RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [192.18.6.24] (HELO gmp-eb-inf-2.sun.com) (192.18.6.24) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 24 Jun 2008 08:31:11 +0000 Received: from fe-emea-10.sun.com (gmp-eb-lb-2-fe3.eu.sun.com [192.18.6.12]) by gmp-eb-inf-2.sun.com (8.13.7+Sun/8.12.9) with ESMTP id m5O8VLfm004008 for ; Tue, 24 Jun 2008 08:31:22 GMT Received: from conversion-daemon.fe-emea-10.sun.com by fe-emea-10.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) id <0K2Y00F01KG06500@fe-emea-10.sun.com> (original mail from Knut.Hatlen@Sun.COM) for derby-user@db.apache.org; Tue, 24 Jun 2008 09:31:21 +0100 (BST) Received: from localhost ([129.159.112.134]) by fe-emea-10.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) with ESMTPSA id <0K2Y00DKBKZZRL00@fe-emea-10.sun.com> for derby-user@db.apache.org; Tue, 24 Jun 2008 09:31:11 +0100 (BST) Date: Tue, 24 Jun 2008 10:31:11 +0200 From: Knut Anders Hatlen Subject: Re: any feedback on this? In-reply-to: <4860372E.4030505@sun.com> Sender: Knut.Hatlen@Sun.COM To: Derby Discussion Message-id: Organization: Sun Microsystems MIME-version: 1.0 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT References: <248012.88193.qm@web31807.mail.mud.yahoo.com> <485FB1AA.4060701@sun.com> <486034E1.4010408@sun.com> <48603599.9010807@amberpoint.com> <4860372E.4030505@sun.com> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.0.60 (usg-unix-v) X-Virus-Checked: Checked by ClamAV on apache.org Alan Burlison writes: > Bryan Pendleton wrote: > >> I store my critical string fields twice, in two separate columns, one >> column has the user-desired case, and the other column has the same data >> in all upper case. >> >> Then, when I want to search without considering case, I take my search >> terms, convert them to all upper case, and search against the >> upper-case column. > > That's what I thought, thanks for the confirmation. One question: if > I use something like > where upper_col = UPPER(?) > will Derby still use an index, or should I convert the case of the > search term externally then do > where upper_col = ? Both of the above queries would normally use the index on upper_col, if such an index is present. -- Knut Anders