OK, I freely admit I've been too busy today to read *all* the discussion on
case-insensitive sorting.

Let me outline my solution to the problem. This uses the SQL "COLLATE"
clause, and InterBase's ability to add collation drivers to an installed
server. I've created a set of drivers that mimic each of the existing
InterBase European collations, EXCEPT they make no distinction based on
case. To use them:

CREATE TABLE example (someText CHARACTER(30) COLLATE FR_FR_NOCASE, /*
Case insensitive FRench used in FRance driver */
someMore CHARACTER(30) COLLATE DE_DE_NOCASE) /*
Case insensitive German used in Germany driver */

Indices created on these columns are automatically case insensitive.
Comparisions using these columns automatically use the rules for the column

eg:
... WHERE someText = 'Des Jardins' /* Uses
FR_FR_NOCASE */
AND someMore < 'Schwartz' /* Uses
DE_DE_NOCASE */
OR /* if you don't like the column default collation, you can
specify one */
someText = 'Garden' COLLATE EN_UK_NOCASE /* Overrides
default of FR_FR_NOCASE */
AND
someText = someMore COLLATE SJIS_NOCASE /* Silly in
practice, but, yes, you can even specify
Japanese
character comparision */

Performance: Slightly faster than existing InterBase collation drivers.
Memory: Small, about 2K per additional driver

I was driving myself to complete this product last December. Around
December 18th I suddenly lost motovation (as I'm sure most of you understand
why...)

Current state: I have plenty of motovation to finish this, I need about 2
solid days to spend on it, which I will have in April, since a lot of
projects are finished or in automatic mode (finally!).

And, yes, it will work on InterBase 4.x, 5.x, and 6.x.

(Now, your job (IB-Architect list) is to give me encouragement to finish
this up and post it!)

Dave Schnepper



-----Original Message-----
From: Jim Starkey [mailto:jas@...]
Sent: Wednesday, March 29, 2000 8:22 AM
To: IB-Architect@onelist.com
Subject: [IB-Architect] Syntax for case insensitive sort


From: Jim Starkey <jas@...>

The current SQL "order by" clause is

{ <expr> | <position> } [ ASC | DESC ]

We could make it

{ <expr> | <position> } [ ASC | DESC ] [CASE SENSITIVE | CASE
INSENSITIVE]

Obviously the default would "CASE SENSITIVE".

I'm not at all keen on two word "keywords", but CASEINSENSITIVE is too
ughly to consider. Just sensitive makes the whole things a little
too touchy feely for my taste, though INSENSITIVE is a pretty good
description for computer code. In any case (ho ho), once we open
the gates on collation options many more will try to follow, so perhaps
a common qualifier CASE is justified. That also leaves the unadorned
keyword SENSITIVE for the day when the database system knows to put
the poetically inclined first and fiftyish iconoclastic programmers
last.

Jim Starkey

------------------------------------------------------------------------
Special Offer-Earn 300 Points from MyPoints.com for trying @Backup
Get automatic protection and access to your important computer files.
Install today:
http://click.egroups.com/1/2344/3/_/_/_/954347030/
------------------------------------------------------------------------

To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com