It sounds like SQL actually made a sensible design decision in this
case.

How this would work in Ansi sql (taking liberties with syntax, etc)

create collation NOCASE based on existing_collation (vendor specific
options to specify case insignificant)
....
SELECT ... ORDER BY afield COLLATE NOCASE
.... GROUP BY afield COLLATE NOCASE
.... WHERE afield = 'ABC' COLLATE NOCASE

etc. Not as easy as just "NOCASE" as you need the word "COLLATE".

However, if afield is defined to have default collation "NOCASE"
you never have to worry about it -- all comparisions will follow
nocase by default for that field.

InterBase already supports the alternate collation methods, except
for the "create collation" SQL clause -- it needs to be done with a
stored procedure instead of a SQL statement.

Dave

--- In IB-Architect@egroups.com, Tim Uckun <tim@d...> wrote:
> At 04:28 PM 08/02/2000 -0500, you wrote:
>
> >BTW, does anyone know if other DB's such as Oracle have this
feature?
>
> Oracle does not. It does let you create an index based on a
function
> though. Their work around is to create a function index on UPPER
(something)
> and then to specify that index when searching for upper(something).
This
> way you can use a function and still have the use of an index.
Pretty lame.
>
> Sybase has a case insensitive collation option when you create the
> database. Once you specify this every query in your database is
case
> insensitive on every text field including where, order by, group by
etc. I
> think SQL anywhere is the same but I am not sure.
>
> MS-SQL server is case insensitive by default although you can
specify case
> sensitive if you want.
>
> Postgres is case sensitive but they have a list of regexp operators
for
> doing case insensitive searches (~* for case insensitive match) and
you can
> overload most operators to provide case insensitive options. The
like
> operator (~~) can be overridden very easily but overriding the =
operator
> causes problems. I have thought of actually rewriting the varlena
(varchar)
> string functions to provide case insensitive operations but I do
not know
> what ripple effects that might have. One thing for sure it would
throw off
> the optimizer estimates. Postgres can also use locale settings but
I do not
> know of any latin case insensitive locales created for linux.
>
> Sorry no experience with informix.
>
>
>
>
>
> :wq
> Tim Uckun
> Due Diligence Inc. http://www.diligence.com/ Americas Background
> Investigation Expert.
> If your company isn't doing background checks, maybe you haven't
considered
> the risks of a bad hire.