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