db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: any feedback on this?
Date Tue, 24 Jun 2008 12:40:51 GMT
Bryan Pendleton wrote:
>> How do people work around this at present?  I absolutely *must* have 
>> case-insensitive indexed searches in my application.
>
> 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.
>
> thanks,
>
> bryan
>
>
Some people have found it useful to add a CHECK constraint to the table 
to verify that wrong values aren't leaking into the upper-case column 
from somewhere in their application. Something like the following can 
give you more peace of mind:

create table t
(
   lastName varchar( 50 ),
   normalizedLastName varchar( 50 ),
   check ( normalizedLastName = upper( lastName ) )
);

Hope this helps,
-Rick

Mime
View raw message