SEO

#1 (**permalink**) 04-20-2008, 11:30 AM

Hello All,

Wondering about the reasoning behind doing something like.....

create index some_index on employee
(
toupper (department_name)
);

1. Any idea why, not in this specific case, but is this standard ?

2. Would this not be wastefull as opposed to just indexing the department
name and ensuring that it's case is changed on insert (I guess this speaks
to my burgeoning understanding of Informix indexes)?

Thanks in advance for the thoughts.

Tam.

#2 (**permalink**) 04-20-2008, 11:30 AM

> 1. Any idea why, not in this specific case, but is this standard ?
not standard, just convient i guess.

> 2. Would this not be wastefull as opposed to just indexing the department

consider names lname, fname ; i would like to see names in a database
to be Upper for te first char and lower for the rest; the way it is
written
on a letter for example.

Querying this may not always come back with the results wanted;
(typos no upper first char etc) therefor i would use a func index
toupper to find
them all regardless of the case.

Superboer.

#3 (**permalink**) 04-20-2008, 11:31 AM

Superboer wrote:
> > 1. Any idea why, not in this specific case, but is this standard ?
> not standard, just convient i guess.
>
> > 2. Would this not be wastefull as opposed to just indexing the department
>
> consider names lname, fname ; i would like to see names in a database
> to be Upper for te first char and lower for the rest; the way it is
> written
> on a letter for example.
>
> Querying this may not always come back with the results wanted;
> (typos no upper first char etc) therefor i would use a func index
> toupper to find
> them all regardless of the case.
>
>
> Superboer.

I really would prefer a case insensitive testing operator or character
set. I remember doing this in 1991 with Sybase. The database would
store upper and lower case but could be set up to ignore case for
comparisons. I think this can be done with Informix but I can't seem to
figure out how to do it, other than using a functional index.

#4 (**permalink**) 04-20-2008, 11:31 AM

bozon wrote:
> Superboer wrote:
>
>>>1. Any idea why, not in this specific case, but is this standard ?
>>
>>not standard, just convient i guess.
>>
>>
>>>2. Would this not be wastefull as opposed to just indexing the department
>>
>>consider names lname, fname ; i would like to see names in a database
>>to be Upper for te first char and lower for the rest; the way it is
>>written
>>on a letter for example.
>>
>>Querying this may not always come back with the results wanted;
>>(typos no upper first char etc) therefor i would use a func index
>>toupper to find
>>them all regardless of the case.
>>
>>
>>Superboer.
>
>
> I really would prefer a case insensitive testing operator or character
> set. I remember doing this in 1991 with Sybase. The database would
> store upper and lower case but could be set up to ignore case for
> comparisons. I think this can be done with Informix but I can't seem to
> figure out how to do it, other than using a functional index.

Informix treats all character data as case sensitive, there is no direct way
around that. Indirect ways are:

1) Use a functional index like the one shown (actually since functional
indexes require the function to be invariant and the upper() function is NOT
marked invariant in the catalog you can't use it directly. You can either
create your own SPL invariant function that calls upper() or writa a C or
Java UDR that's invariant and index using that.)

2) Create a User Defined Type (UDT) that implements case insensitive
comparisons. Define a new type and write your own <, <=, >=, >, & =
operations on that type plus an implicit conversion from CHAR to 'case
insensitive char'.

Art S. Kagel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Indexing A Function???