Re: Case insensitive selects?

On Fri, 16 Feb 2001, Bruce Momjian wrote:

> I was wondering if we could do case-insensitive index waking by doing
> looking for CAR as:
>
>     CAR
>     CAr
>     CaR
>     Car
>     cAR
>     cAr
>     caR
>     car
>
> Basically you look for CAR, then back up in the btree, to CA and look
> for r instead of R.  I relized the number of tests would exponentially
> explode, but isn't it just like btree walking where we back up to test
> the lowercase of the letter.

Wouldn't it be more efficient to just have a single, case-insensitive
index, and then have the query engine automagically compare to the index
in a case-insensitive way? I'm assuming that this is the sort of approach
MS takes, which is why one has to choose the sort order at installation
time. If I choose case-insensitive Unicode, then I would expect the server
to do these things for me behind the scenes:

* When I create an index, automatically convert all char/varchar/text
  fields with lower().
* When I do a query, automatically use lower() on all fields and values
  queried against.

The result would be the same as Mitch describes, only I don't have to do
the work in my queries. The database would assume I want case-insensitive
matching based on some configuration I set, and do all the lower()s for
me. Perhaps the configuration could be set on a per-database basis (like
character set now is with multibyte).

Does that make sense?

Best

David