Subject Re: [IB-Architect] Re: Creating a case-insensitive collation set
Author Tim Uckun
At 04:28 PM 08/02/2000 -0500, you wrote:

>BTW, does anyone know if other DB's such as Oracle have this feature?

Oracle does not. It does let you create an index based on a function
though. Their work around is to create a function index on UPPER(something)
and then to specify that index when searching for upper(something). This
way you can use a function and still have the use of an index. Pretty lame.

Sybase has a case insensitive collation option when you create the
database. Once you specify this every query in your database is case
insensitive on every text field including where, order by, group by etc. I
think SQL anywhere is the same but I am not sure.

MS-SQL server is case insensitive by default although you can specify case
sensitive if you want.

Postgres is case sensitive but they have a list of regexp operators for
doing case insensitive searches (~* for case insensitive match) and you can
overload most operators to provide case insensitive options. The like
operator (~~) can be overridden very easily but overriding the = operator
causes problems. I have thought of actually rewriting the varlena (varchar)
string functions to provide case insensitive operations but I do not know
what ripple effects that might have. One thing for sure it would throw off
the optimizer estimates. Postgres can also use locale settings but I do not
know of any latin case insensitive locales created for linux.

Sorry no experience with informix.

Tim Uckun
Due Diligence Inc. Americas Background
Investigation Expert.
If your company isn't doing background checks, maybe you haven't considered
the risks of a bad hire.