Subject RE: [IB-Architect] Syntax for case insensitive sort
Author David Schnepper
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:

Case insensitive FRench used in FRance driver */
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

... WHERE someText = 'Des Jardins' /* Uses
AND someMore < 'Schwartz' /* Uses
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 */
someText = someMore COLLATE SJIS_NOCASE /* Silly in
practice, but, yes, you can even specify
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

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
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

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

Jim Starkey

Special Offer-Earn 300 Points from for trying @Backup
Get automatic protection and access to your important computer files.
Install today:

To unsubscribe from this group, send an email to: