Unfortunately, you lose the benefit on an index to help make it a quick
search.

What I have found that works is to use a trigger (IB supports BEFORE
triggers) to maintain a mirror column that contains the upper case
equivalent of the mixed case column.

Then, your query will look something like this:

select * from mytable where field1_UC = UPPER(field2)

where field1_UC is derived from UPPER( field1)

Don't forget to put an index on field1_UC or you may as well use
PPER( field1 ).

HTH,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com

----- Original Message -----
From: "Ungod" <ungod@...>
To: <IBDI@egroups.com>
Sent: Saturday, June 24, 2000 8:00 AM
Subject: RE: [IBDI] Porting from MS SQL Server 7.0 to Interbase


> err, issue 1 is a no-brainer.
>
> select * from mytable where UPPER(myfield) = somevalue
>
> or even
>
> select * from mytable where UPPER(field1) = UPPER(field2)
>
> --
> Ungod (W.King)
> www.bytamin-c.com
> www.magick.tm
> I found the answer, now where'd I put that problem...
>
>
> -----Original Message-----
> From: johnlim@... [mailto:johnlim@...]
> Sent: Saturday, 24 June 2000 11:22 PM
> To: IBDI@egroups.com
> Subject: [IBDI] Porting from MS SQL Server 7.0 to Interbase
>
>
> Hello everyone,
>
> We have an Web based application running on MS SQL Server 7.0 that we
> are considering porting to Interbase 6 because of the free licensing.
>
> I am not an expert on Interbase but have identified the following
> issues:
>
> 1. Interbase does not support case-insensitive comparisons, except
> through extensions such as CONTAINS.
>
> 2. Interbase does not support the TOP extension to SELECT, as in
> SELECT TOP 10 * FROM PRODUCTS. This is needed by some 3rd party
> libraries we are using.
>
> 3. Interbase uses || for string concat, SQL Server uses +.
>
> I am sure there are other differences that I am not aware of. Only
> issue (1), case in-sensitivity is show-stopper to us. We can live
> with the other differences.
>
> Some Questions
> --------------
>
> Has anyone have any experience porting from MS SQL Server to
> Interbase?
>
> Has anyone developed a case-insensitive character set for Interbase
> for ISO Latin-1 ?
>
> Can we develop such a character set with the new Interbase 6
> collation kit?
>
> Will the collation kit support Visual C++? We don't own a copy of
> Borland C++.
>
> Thanks again
> John Lim
>
>
> ------------------------------------------------------------------------
> IT Professionals: Match your unique skills with the best IT projects at
> http://click.egroups.com/1/3381/5/_/679568/_/961852938/
> ------------------------------------------------------------------------
>
> Community email addresses:
> Post message: IBDI@onelist.com
> Subscribe: IBDI-subscribe@onelist.com
> Unsubscribe: IBDI-unsubscribe@onelist.com
> List owner: IBDI-owner@onelist.com
>
> Shortcut URL to this page:
> http://www.onelist.com/community/IBDI
>
>
>
> ------------------------------------------------------------------------
> IT Professionals: Match your unique skills with the best IT projects at
> http://click.egroups.com/1/3381/5/_/679568/_/961858874/
> ------------------------------------------------------------------------
>
> Community email addresses:
> Post message: IBDI@onelist.com
> Subscribe: IBDI-subscribe@onelist.com
> Unsubscribe: IBDI-unsubscribe@onelist.com
> List owner: IBDI-owner@onelist.com
>
> Shortcut URL to this page:
> http://www.onelist.com/community/IBDI
>