on the feature-request list, function-based indeces would be nice for this. we could, for example,
setup an index on the result of calling upper() on a field. i know oracle has this, and firebird
already has asc/desc indeces on columns and sets of columns. i'm sure others can contribute more
uses for a function-based index ... but i know that upper() is one application that comes up all
the time for me.

does firebird support an index on a calculated column? if so, would it support a calculated column
as being the result of calling upper() on a 'base' column? that would, effectively, be a function-
index ...

-philip

---------- Original Message -----------
From: "Arno Brinkman" <firebird@...>
To: <ib-support@yahoogroups.com>
Sent: Thu, 17 Apr 2003 13:31:52 +0200
Subject: Re: [ib-support] Migrating from Microsoft SQL Server to Firebird

> Hi,
>
> > 1) In Microsoft SQL Server, there is an option to set
> > string searches to be case-insensitive for the entire database.
> > So, where clauses like those written below works:
> >
> > WHERE id = 'name' (returns rows with id = 'name', 'Name', 'NAME')
> > WHERE id like '%name%' (returns rows with id
> > = 'mYname', 'NameMe', 'NAME')
> >
> > Besides using WHERE upper(id) = upper('name'), is there any
> > alternative options for setting a field, a table or a database
> > to be case-insensitive?
>
> - You could use case-insensitive collations to do it!
>
> - Note that "WHERE upper(id) = upper('name')" doesn't use an index. If you
> want an index for these fields you can create an shadow colomn which is
> upped with a trigger and contain the UPPER version of the source colomn.
>
> > 2) Is there an isnull(evalValue, ifnull) functionality in
> > Firebird? The isnull function returns the value stored in ifnull
> > when the evalValue is null.
>
> FB1.5 has COALESCE() (MSSQL has it also) and this is an SQL-standard
> function.
>
> > 3) There is a length limit to field names, constraint names and
> > table names. Any way to extend that limit?
>
> The hard-coded limit is 31 characters IIRC.
>
> Regards,
> Arno Brinkman
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

------- End of Original Message -------