case insensitive select
Carol asked how to do case-insensitive searches when UPPER() seemed unavailable. Replies: UPPER()/LOWER() exist in 7.3 but prevent index use, so they're slow; MATCHES with character classes (e.g. "[Cc][Aa]...") can use an index as a brute-force workaround. The consensus best fix was to add a redundant all-uppercase copy of the column, index it, and search that (maintained via triggers), or move to XPS/IDS.2000 for functional indexes; the Excalibur Text DataBlade (9.x) was also suggested. One poster asked for help writing the insert/update trigger and got no answer.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Dear all, UPPER(cust_name) is not found in the informix SQL. Then, how can i get a case insensitve select statement? Thx a lot! Carol
Carol wrote: > UPPER(cust_name) is not found in the informix SQL. Then, how can i get a > case insensitve select statement? There's an upper() function in 7.3! But it's a joke (at least on our machine): It is _slower_ than MATCHES! :-( Carol you can also write e.g.: WHERE cust_name MATCHES "[Cc][Aa][Rr][Oo][Ll]" Kind regards, Joachim Engel Engel EDV-Systeme http://www.engel-edv.de mailto:jengel/nospam/@engel-edv.de
Joachim Engel wrote: > > Carol wrote: > > > UPPER(cust_name) is not found in the informix SQL. Then, how can i get a > > case insensitve select statement? > > There's an upper() function in 7.3! > But it's a joke (at least on our machine): > It is _slower_ than MATCHES! :-( That is because MATCHES is often able to use an index. If you use UPPER() or LOWER() in a filter any index on the column are ignored. > Carol you can also write e.g.: > > WHERE cust_name MATCHES "[Cc][Aa][Rr][Oo][Ll]" This is brute force but it will work reasonably well if an index exists. Truth is the best solution is STILL to add an upper or lower case only copy of a mixed case column that must be searched this way and index it. The storage overhead of doing this is usually nominal. The only other solution is to get XPO or IDS.2000 which support functional indexes and user defined index types so that you CAN use UPPER and LOWER to filter just by creating an index on UPPER(column) or LOWER(column). Art S. Kagel
Hello Mr. Kagel,
> Truth is the best solution is STILL to add an upper or lower case only
> copy of a mixed case column that must be searched this way and index it.
> The storage overhead of doing this is usually nominal. The only other
> solution is to get XPO or IDS.2000 which support functional indexes and
> user defined index types so that you CAN use UPPER and LOWER to filter
> just by creating an index on UPPER(column) or LOWER(column).
Small databases can do that too! :-)
E.g. with Centura SQLBase it's no problem to write
the following:
CREATE INDEX indexname ON tablename (@UPPER(colname));
IMHO informix is a poor database system! :-(
Kind regards,
Joachim Engel
Engel EDV-Systeme
http://www.engel-edv.de
mailto:jengel@engel-edv.de
Dear Newsgroup,
I'm sorry, if I had hurt anybody by my statement. :-/
I got a rude affront per e-mail by somebody,
(which I deleted immediately)
because of calling Informix "a poor database system".
Please, don't get me wrong!
It's not the first disapointment in respect of
the (language) capacity or speed of Informix.
Let me tell only 2 different examples:
- I'm missing powerful string and data type conversion functions.
- Performance gap between OR and UNION (p**r optimizer!)
We are realizing very powerful client applications since years
(e.g. our user search functions can compete with other's report
functions)
and we often reach the limits of Informix, where we can't go further;
But I have already been further with other systems.
Perhaps I know too less about Informix,
perhaps you can help me to solve some problems! :-)
If I get useful answers in this NG, I'll watch it in future constantly.
Here my actual contribution to this NG:
For searching the entry "Smit*" case-insensitive much faster
than my suggestion before, just try:
select surname from contact
where ((surname matches "S*") or (surname matches "S*"))
and ((surname matches "?M*") or (surname matches "?m*"))
and ((surname matches "??I*") or (surname matches "??i*"))
and ((surname matches "???T*") or (surname matches "???t*"));
It's cumbersome, but it works! :-)
(uses the indexes I guess ...)
Hope this helps,
Joachim Engel
Engel EDV-Systeme
http://www.engel-edv.de
mailto:jengel/nospam/@engel-edv.de
In article <38037810.2B08@engel-edv.de>, Joachim Engel <jengel@engel-
edv.de> writes
>Dear Newsgroup,
>
>I'm sorry, if I had hurt anybody by my statement. :-/
>I got a rude affront per e-mail by somebody,
>(which I deleted immediately)
>because of calling Informix "a poor database system".
>
>Please, don't get me wrong!
>It's not the first disapointment in respect of
>the (language) capacity or speed of Informix.
>
>Let me tell only 2 different examples:
>- I'm missing powerful string and data type conversion functions.
>- Performance gap between OR and UNION (p**r optimizer!)
>
>We are realizing very powerful client applications since years
>(e.g. our user search functions can compete with other's report
>functions)
>and we often reach the limits of Informix, where we can't go further;
>But I have already been further with other systems.
>
>Perhaps I know too less about Informix,
>perhaps you can help me to solve some problems! :-)
>
>If I get useful answers in this NG, I'll watch it in future constantly.
>
>Here my actual contribution to this NG:
>
>For searching the entry "Smit*" case-insensitive much faster
>than my suggestion before, just try:
>
Add a column to the tables which has the value in uppercase. Then
search on that where ucol matches "SMIT*". That is much faster.
> select surname from contact
> where ((surname matches "S*") or (surname matches "S*"))
> and ((surname matches "?M*") or (surname matches "?m*"))
> and ((surname matches "??I*") or (surname matches "??i*"))
> and ((surname matches "???T*") or (surname matches "???t*"));>
>It's cumbersome, but it works! :-)
>(uses the indexes I guess ...)
>
>
>Hope this helps,
>
>Joachim Engel
>Engel EDV-Systeme
>http://www.engel-edv.de
>mailto:jengel/nospam/@engel-edv.de
--
David Williams
I've come across this problem too and my initial solution was to update a
mirrored column in the table with the uppercase version. If you put the
uppercasing in an update and insert trigger, you'd think this would be a
snack. Then index the uppercase field and do your search comparisons on
that. But I'm damned if I can write the update and insert triggers! The
following wont work. Any ideas??
CREATE TRIGGER tri_composer INSERT on composer
REFERENCING NEW as post
FOR EACH ROW( update composer set _ulastname = upper( post.lastname )
where composerid = post.composerid );
Thanks
Mike
Joachim Engel <jengel@engel-edv.de> wrote in article
<38058A39.6B94@engel-edv.de>...
> Hi David,
>
> > Add a column to the tables which has the value in uppercase. Then
> > search on that where ucol matches "SMIT*". That is much faster.
>
> Thanks for the tip.
> You can't do that for every column, can you?
>
> As I mentioned we do have powerful search functions
> for our users. This may unusual for many applications. ;-)
>
>
> Kind regards,
>
> Joachim Engel
> Engel EDV-Systeme
> http://www.engel-edv.de
> mailto:jengel/nospam/@engel-edv.de
>
Hi David, > Add a column to the tables which has the value in uppercase. Then > search on that where ucol matches "SMIT*". That is much faster. Thanks for the tip. You can't do that for every column, can you? As I mentioned we do have powerful search functions for our users. This may unusual for many applications. ;-) Kind regards, Joachim Engel Engel EDV-Systeme http://www.engel-edv.de mailto:jengel/nospam/@engel-edv.de
Joachim Engel (jengel@engel-edv.de) wrote: : Hi David, : > Add a column to the tables which has the value in uppercase. Then : > search on that where ucol matches "SMIT*". That is much faster. : Thanks for the tip. : You can't do that for every column, can you? : As I mentioned we do have powerful search functions : for our users. This may unusual for many applications. ;-) You really may want to get ahold of a friendly Informix Sales guy and talk to them about the Excalibur Text Datablade (although it only works with version 9.X of the engine). While I don't know much about it, I do know it is there to make searching easier.