Why not use a function like:

select field1,field2,ucase(field3) from atable
where ucase(field2)='ABC'
order by ucase(field1)

ucase for uppercase, lcase for lowercase.

Internally the functions should be part of the core engine, and thus
wouldnt make any extra overhead compared to the suggested NOT CASE
directive.

Further I believe that case sensitiveness should be implemented on field
level on creation of a table, which means that indexes aut. will follow the
sensitiveness of the field they index. Fields could be generated like:

CREATE TABLE atable (FIELD1 VARCHAR(15) NOT NULL UCASE, FIELD2 VARCHAR(15)
NOT NULL, FIELD3 VARCHAR(15) NOT NULL LCASE);

directive UCASE for aut. converting data to uppercase according to
characterset specified for the database or field.
directive LCASE for aut. converting data to lowercase --"--
directive NOCASE (default) for storing data as they are, without
conversion.

Rules of comparing strings would be to convert from less restrictive case
to more restrictive on comparison:
.
Comparing FIELD1 ("ABC") and FIELD2 ("abc") would result in TRUE because
FIELD2 is compared with FIELD1 and FIELD2 is less restrictive (defined as
NCASE).
Comparing FIELD1("ABC") and FIELD3("abc") would result in FALSE since they
are equally restrictive.
Comparing FIELD2("AbC") with "ABC" would result in FALSE.
Comparing FIELD2("AbC") with "AbC" would result in TRUE.

Just my 5 cents.

best regards

Kim Madsen
kbm@...



----------

> From: Jim Starkey <jas@...>
> To: IB-Architect@onelist.com
> Subject: RE: [IB-Architect] Syntax for case insensitive sort
> Date: Wednesday, March 29, 2000 8:08 PM
>
> From: Jim Starkey <jas@...>
>
> At 09:47 AM 3/29/00 -0700, you wrote:
> >From: "David Berg" <DaveBerg@...>
> >
> >An alternative to declaring case insensitivity in the order by clause is

to

> >declare it as an attribute of the field. This has a number of

advantages:

> >
> >(1) Indexes that include the field are case insensitive. This makes a

BIG

> >difference in performance when doing case insensitive sorts or queries.
> >
>
> Case insensitive indexes are obviously required [their implementation
> is essentially trivial and has no performance implications].
>
> >(2) Order by clauses are automatically case insensitive (and can use
> >indexes).
> >
>
> A case insensitive equality plus a case insensitive index is an easy
> case optimize. I'm not sure you always want a case insensitive ordering.
> If this is true, then you need the modifier on ORDER anyway, and
> having differing defaults for its absence on different fields could
> be quite confusing. There is some merit to being able to look at
> a select statement and know what it does...
>
> >(3) Comparisons (<,>,=) are case insensitive (perhaps this should be an
> >option).
> >
>
> We need some additional operators. Definitely.
>
> >(4) Unique constraints are case insensitive. So I can't have

"Interbase"

> >and "INTERBASE" both in a unique index.
> >
>
> I'm not very comfortable with the combination of case insensitivity
> and unique contraints. They seem a little unnatural. We could
> invent some rules, but it strikes me as unwise.
>
>
> The difficult case is when somebody wants to join a case
> insensitive field in one table to a case sensitive field
> in another (this would be a very common operation during
> the transition period or during data importation). What
> are the rules? If you say a comparison between case sensitive
> and case insensitive values is case insensitive then the
> system couldn't optimize the join with the case sensitive
> index. If you reverse the rule, then all comparisons with
> string constants (necessarily case sensitive) don't work.
>
> A good rule: When caught between a rock and a hard place,
> go somewhere else.
>
> Jim Starkey
>
> ------------------------------------------------------------------------
> Get your bargains at AndysGarage.com!
> http://click.egroups.com/1/2579/3/_/_/_/954349830/
> ------------------------------------------------------------------------
>
> To unsubscribe from this group, send an email to:
> IB-Architect-unsubscribe@onelist.com
>
>
>