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
