Andrew Hamm wrote:

> How could i forget this one?
>
> case insensitive comparisons and collating order!
>
> I don't want that applied to CHAR and VARCHAR types, since they are
> fast'n'mean byte-wise datatypes. However, since NCHAR and NVARCHAR fields
> can have a GLS locales applied, they are ideal for this little extension.
>
> I cannot find an English Locale that has case-insensitive collation. I don't
> know about other european GLS locales, but I'd wager they are case sensitive
> too, but could profit from caseless.

Can't this be achieved using functional indexes?

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /|
| Mydas Solutions Ltd http://MydasSolutions.com |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+


sending to informix-list

Mark D. Stock wrote:
>
> Can't this be achieved using functional indexes?

ummmmm, we'd have to deliver 9.X to all customers....

"Andrew Hamm" <ahamm@mail.com> wrote ...
> Mark D. Stock wrote:
> >
> > Can't this be achieved using functional indexes?
>
> ummmmm, we'd have to deliver 9.X to all customers....

and we'd have to change ALLLLLLLL the code to have something like

and upshift(cats_name) = upshift(?)

instead of

and cats_name = ?

wouldn't we? I've not used functional indexes before (note to self...) but
my understanding is that the engine needs to recognise an expression that is
solvable with the index as it was defined.

Also, can a functional index solve this?

and cats_name matches "tidd*l*s"

See, we want case to be ignored. SOMETIMES. When we ask for it ONLY. Without
having to rewrite lots of code.

Andrew Hamm wrote:
>
> Also, can a functional index solve this?
>
> and cats_name matches "tidd*l*s"
>

That example would be better with

and cats_name matches "*tidd*l*s"

so that it finds these records:

Tiddles
TIddles
tiddles
TIDDLES
Mr Tiddles
Sir Tiddles
Tiddlz