Thanks Helen,

I have tried using character set ISO8859_1 with collation sequence EN_US.
This seems to make sorting case insensitive without going through the
addition of an extra column. Of course enforcing case insensitive uniqueness
still requires an extra column.
I could not find IB specific info on collation sequences and did not make
thorough testing, so I am not sure if the EN_US is really case insensitive.

Regards
----------------------------------------------

Date: Sun, 24 Jun 2001 02:44:55 +1000
From: Helen Borrie <helebor@...>
Subject: Re: Insensitive Case

At 03:33 AM 23-06-01 +0300, you wrote:

>How can we have records sorted alphabetically with case insensitivity in
>IB6?
>Is it possible to enforce case insensitive uniqueness in a field?

Add an extra column (proxy column for the column on which you want the
case-insensitive attributes). Make sure the original column has a NOT NULL
constraint on it.

ALTER TABLE MYTABLE
ADD MyCol2 VARCHAR(n)
NOT NULL UNIQUE
COLLATE <collation order>;

Create Before Insert and Before Update triggers to populate the column:

..
CREATE TRIGGER POP_MYCOL2_BI
FOR MYTABLE
ACTIVE BEFORE INSERT AS
BEGIN
MYCOL2 = UPPER(MYCOL);
END

CREATE TRIGGER POP_MYCOL2_BU
FOR MYTABLE
ACTIVE BEFORE UPDATE AS
BEGIN
IF (NEW.MYCOL <> OLD.MYCOL) THEN
NEW.MYCOL2 = UPPER(NEW.MYCOL);
END

If you have existing data, populate the new column with a DSQL statement
UPDATE MYTABLE SET MYCOL2=UPPER(MYCOL);
Note that you will get constraint violation errors if you have any existing
data in MyCol in different rows, that would uppercase to an identical
string, e.g. MacDonald and Macdonald.

To get your case-insensitive ordering, just ORDER BY the proxy column.

Cheers,
Helen


All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________


________________________________________________________________________
________________________________________________________________________



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/




_________________________________________________________
Do You Yahoo!?
Get your free @... address at http://mail.yahoo.com