Re: Indexed case-insensitive SELECT queries?

In version 8+ I believe you can build a function index on a field it's great.

Patrick Lanphier

Jason Hoos wrote:

> Is it possible to set up a table so that you can do SELECT queries off of a
> text field that are not case-sensitive?
>
> In other words, I want to be able to do a query equivalent to:
>
> SELECT * FROM table WHERE NLSSORT(name, 'WEST_EUROPEAN' = NLSSORT('bob')
>
> except that I want it to utilize an index. Unfortunately, as best as I can
> tell from the Oracle documentation, Oracle only builds indexes using the
> binary representation of a field, and I can't find a way to convince it to
> do otherwise. Is there a way that I overlooked? We are currently using
> Oracle 7.3, although we may start using 8 soon if that helps any. I would
> really like to avoid having to add an extra uppercased-column to the table
> in question just to do searches on.
>
> Thanks,
> Jason Hoos
Received on Thu Nov 04 1999 - 16:36:00 CST