Hi Thomas,

> I'm confused ...
>
> If you have a character field with a case-senstive collation, then
> querying for UPPER without an function-based index shouldn't use an index.
>
> If you have a character field with a case-insenstive collation, then my
> understanding is that you don't have to use UPPER at all, to perform a
> case-insensitive query, thus a regular, non-function-based index on the
> field with a case-insensitve collation should be sufficient.

sorry for the confusion.

querying for UPPER comes from the "old" application, where I was using character set ISO8859_1 and no default collation. Also when I started working on the "new" application, I first was using UTF8 and UNICODE as collation.

So far querying for UPPER was ok and some queries in my code were using it.

Only recently I've discovered the ability to use a default collation in general and also the case-insensitive UNICODE_CI_AI, so I've tried this, while the quieries were still using the UPPER calls.

After posting the initial message, I then spent some more thoughts on that and came to the same conclusion: it doesn't make sense to use UPPER calls in SQL statements anymore, if I create my database with a case-insensitive default collation.

Should have think a bit more before posting maybe, but I'm looking at too many "new" things at once right now, so maybe it was due to a loss of an overview - sorry :)

Thanks for the help nevertheless!

Patrick