Re: Case insensitive selects?

On Thu, 15 Feb 2001, Tom Lane wrote:

> Not then either; you'd need to write
>
> SELECT *
> FROM   mime_type
> WHERE  LOWER(name) = LOWER('text/HTML');
>
> or equivalently
>
> SELECT *
> FROM   mime_type
> WHERE  LOWER(name) = 'text/html';
>
> which is what will result from constant-folding anyway.

Yes, of course; my oversight.

> The details of invocation seem beside the point, however.  The point is
> that a btree index is all about sort order, and the sort order of data
> viewed case-sensitively is quite different from the sort order of
> monocased data.  Perhaps in an ASCII universe you could play some tricks
> to make the same index serve both purposes, but it'll never work in
> non-ASCII locales ...

Hmmm...somehow, MS gets it to work in SQL Server. Lord knows how (or if
it's effective or fast), but I won't worry about it (since the last
thing I want to do is switch to NT!). I'll just code more carefully per
the examples above to ensure proper index use.

Thanks,

David