Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
Date: Fri, 02 Nov 2007 05:21:09 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: Case insensitive indexed search possible?
In-reply-to: <005c01c81cd9$eb611bf0$e3118780@skate>
Sender: Richard.Hillegas@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <472B1635.3000406@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <005c01c81cd9$eb611bf0$e3118780@skate>
User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728)

Hi Jon,

The situation with case-insensitive searching did not change in the 
latest 10.3 release and no one has volunteered to address this issue in 
the next feature release, 10.4. If you want to avoid the full table 
scan, you can store the text in  two columns:  mixed case in one column, 
and normalized to lowercase in the second column.

Regards,
-Rick

Jon Miller wrote:
> Hi all,
>
> Does anyone know if there is a way to do a case insensitive indexed 
> search? I know in the past this wasn't possible, but, I'm wondering if 
> this might not be fixed in the version that was recently released. 
> Note, I know you can do a LOWER() to do it. However, I don't want to 
> do that because I don't want it to do a table scan. Ideally, it would 
> be possible to make all text searches case insensitive with a single 
> setting.
>
> Jon
>