Case Insensitivity

Hi Folks, just checking what the lastest status is on giving us case insensitive searches etc in Informix databases. The bloody subject won't go away. Stalled in the planning committee still? Even Orrible 10 is allegedly out now with CI searches. Madison, Jonathan, I'm looking in your direction... And hello to you all, by the way.

Andrew, On 9.4, you can do this: CREATE FUNCTION upshift (value VARCHAR(225)) RETURNING VARCHAR(225) AS value WITH (NOT VARIANT); RETURN UPPER(value); END FUNCTION; CREATE INDEX index_name ON table_name (upshift(column_name)); Note that CREATE INDEX index_name ON table_name (UPPER(column_name)); results in SQL Error (-9844): Invalid function (upper) used in a functional key. hence the need for the SPL function wrapper. To test that it works: SET EXPLAIN ON; SELECT * FROM table_name WHERE (upshift(column_name)) LIKE 'VALUE%'; The "sqexplain.out" file will show the index being used. I know this is only a work-around, but I hope it helps. -- Regards, Doug Lawry www.douglawry.webhop.org "Andrew Hamm" <ahamm@mail.com> wrote in message news:50jb1dF1gaai2U1@mid.individual.net... > Hi Folks, > > just checking what the lastest status is on giving us case insensitive > searches etc in Informix databases. The bloody subject won't go away. > Stalled in the planning committee still? Even Orrible 10 is allegedly out > now with CI searches. > > Madison, Jonathan, I'm looking in your direction... > > And hello to you all, by the way.

Is there any way to do this with character sets? A long time ago when I was doing Sybase development they had a feature that allowed you to do case insensitive searches by using a character set that ignored cases on comparison but still saved the data the same way. Andrew Hamm wrote: > Hi Folks, > > just checking what the lastest status is on giving us case insensitive > searches etc in Informix databases. The bloody subject won't go away. > Stalled in the planning committee still? Even Orrible 10 is allegedly out > now with CI searches. > > Madison, Jonathan, I'm looking in your direction... > > And hello to you all, by the way.

Check this out: http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html Andrew Hamm wrote: > Hi Folks, > > just checking what the lastest status is on giving us case insensitive > searches etc in Informix databases. The bloody subject won't go away. > Stalled in the planning committee still? Even Orrible 10 is allegedly out > now with CI searches. > > Madison, Jonathan, I'm looking in your direction... > > And hello to you all, by the way. >

I wonder if Informix character sets work in a similar way. I looked into it but I didn't get anywhere in the past. It looks like you could set up a custom character set to do this but then you could have all kinds of issues. There has to be at least 1 expert on this on this forum. On Jan 10, 9:24 am, Lucky <l...@lucky-you.com> wrote: > Check this out:http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html > > Andrew Hamm wrote: > > Hi Folks, > > > just checking what the lastest status is on giving us case insensitive > > searches etc in Informix databases. The bloody subject won't go away. > > Stalled in the planning committee still? Even Orrible 10 is allegedly out > > now with CI searches. > > > Madison, Jonathan, I'm looking in your direction... > > > And hello to you all, by the way.

Lucky: > Check this out: > http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html Yeah, something of that order, but actually the opposite problem! Customers want to search by name, using "john" to find John john and JOHN. Personally I would prefer that if a column is declared case insensitive (CI), then also the strings all compare equal for unique indexing etc. I'm not just chasing case insensitive comparisons, but storage too. Bozon: > I wonder if Informix character sets work in a similar way. I looked > into it but I didn't get anywhere in the past. It looks like you could > set up a custom character set to do this but then you could have all > kinds of issues. There has to be at least 1 expert on this on this forum. With NCHAR types? I've been wondering myself if I can create a case-insensitive English character set and then change our chosen columns to NCHAR and NVARCHAR, but the more I read about UNICODE and locales, the more my brain starts to hurt. Doug: > On 9.4, you can do this: Yes, that idea is floating around here, but then all our SQL's will need to be modified to use the upshift function in all WHERE clauses. That's a frightening thought when you have millions of lines of code to deal with. Even if we did this task, the end-users with Crystal Reports etc will probably complain that there is no automagic support for them too. My understanding is that CHAR types use plain old binary comparisons in the traditional manner, hence are very fast. If you use NCHAR, then all comparisons and matching have to be fed through locale-sensitive comparison routines, hence they lose a bit of performance. If there was a CASE INSENSITIVE clause on the column definitions, then I expect a CHAR would have to redirect off at least to a case insensitive comparator function, while an NCHAR CASE INSENSITIVE would probably get away with a barely noticable degredation due to the other work going on for that datatype. I expect CHAR CI would suffer some minor performance hits because of this effect, but it couldn't be major. The work involved adding this attribute in the engine source is not something I'm willing to ponder; perhaps it would be more cost-effective for Informix to simply release CI versions of all their locales rather than dicking with the engine too much.

My brain hurt too looking at the unicode/locale stuff. I don't think that the performance is impacted much because the comparison value is stored in a lookup array. When I looked at the local source files ($INFORMIXDIR/gls/lc11/os/en_US.ISO8859-1.lc for example You get definitions like # # Alphabetic Lower-case to Upper-case Conversion # toupper (\\x61,\\x41);(\\x62,\\x42);(\\x63,\\x43);(\\x64,\\x44);(\\x65,\\x45);\\ (\\x66,\\x46);(\\x67,\\x47);(\\x68,\\x48);(\\x69,\\x49);(\\x6a,\\x4a);\\ (\\x6b,\\x4b);(\\x6c,\\x4c);(\\x6d,\\x4d);(\\x6e,\\x4e);(\\x6f,\\x4f);\\ (\\x70,\\x50);(\\x71,\\x51);(\\x72,\\x52);(\\x73,\\x53);(\\x74,\\x54);\\ (\\x75,\\x55);(\\x76,\\x56);(\\x77,\\x57);(\\x78,\\x58);(\\x79,\\x59);\\ (\\x7a,\\x5a) # # Alphabetic Upper-case to Lower-case Conversion # tolower (\\x41,\\x61);(\\x42,\\x62);(\\x43,\\x63);(\\x44,\\x64);(\\x45,\\x65);\\ (\\x46,\\x66);(\\x47,\\x67);(\\x48,\\x68);(\\x49,\\x69);(\\x4a,\\x6a);\\ (\\x4b,\\x6b);(\\x4c,\\x6c);(\\x4d,\\x6d);(\\x4e,\\x6e);(\\x4f,\\x6f);\\ (\\x50,\\x70);(\\x51,\\x71);(\\x52,\\x72);(\\x53,\\x73);(\\x54,\\x74);\\ (\\x55,\\x75);(\\x56,\\x76);(\\x57,\\x77);(\\x58,\\x78);(\\x59,\\x79);\\ (\\x5a,\\x7a) Which implies and is really the only sensible way to do it a look up array. they had a character and then a comparison value. So for case insensitive you had: In the collating area you have : LC_COLLATE collating-symbol <Weight_2> collating-symbol <Weight_3> collating-symbol <Weight_4> collating-symbol <Weight_5> collating-symbol <Weight_6> collating-symbol <Weight_7> collating-symbol <Weight_8> collating-symbol <Weight_9> collating-symbol <Weight_10> collating-symbol <Weight_11> collating-symbol <Weight_12> collating-symbol <Weight_13> ... collating-symbol <Weight_251> collating-symbol <Weight_252> collating-symbol <Weight_253> collating-symbol <Weight_255> collating-symbol <Weight_256> collating-symbol <Weight_257> order_start forward \\x00 <Weight_2> <Weight_3> <Weight_4> <Weight_5> <Weight_6> <Weight_7> <Weight_8> <Weight_9> <Weight_10> <Weight_11> <Weight_12> <Weight_13> <Weight_14> <Weight_15> <Weight_16> <Weight_17> <Weight_18> <Weight_19> <Weight_20> <Weight_21> ... <Weight_250> <Weight_251> <Weight_252> <Weight_253> <Weight_255> <Weight_256> <Weight_257> \\x01 <Weight_2> \\x02 <Weight_3> \\x03 <Weight_4> \\x04 <Weight_5> \\x05 <Weight_6> \\x06 <Weight_7> \\x07 <Weight_8> \\x08 <Weight_9> \\x09 <Weight_10> \\x0a <Weight_11> \\x0b <Weight_12> \\x0c <Weight_13> \\x0d <Weight_14> \\x0e <Weight_15> \\x0f <Weight_16> \\x10 <Weight_17> \\x11 <Weight_18> ... \\xf8 <Weight_249> \\xf9 <Weight_250> \\xfa <Weight_251> \\xfb <Weight_252> \\xfc <Weight_253> \\xfd <Weight_255> \\xfe <Weight_256> \\xff <Weight_257> order_end END LC_COLLATE This also implies that they are building some lookup tables for this task. So, to make your own case insensitive locale I would change the weights of the upper and lower case letters to the same values for example: \\x41 <Weight_66> # 'A' \\x61 <Weight_66> #'a' I just haven't been pushed to the point of trying it. Andrew Hamm wrote: > Bozon: > > I wonder if Informix character sets work in a similar way. I looked > > into it but I didn't get anywhere in the past. It looks like you could > > set up a custom character set to do this but then you could have all > > kinds of issues. There has to be at least 1 expert on this on this forum. > > With NCHAR types? I've been wondering myself if I can create a > case-insensitive English character set and then change our chosen columns > to NCHAR and NVARCHAR, but the more I read about UNICODE and locales, the > more my brain starts to hurt. > > If you use NCHAR, then all comparisons and matching have to be fed through > locale-sensitive comparison routines, hence they lose a bit of performance. > > If there was a CASE INSENSITIVE clause on the column definitions, then I > expect a CHAR would have to redirect off at least to a case insensitive > comparator function, while an NCHAR CASE INSENSITIVE would probably get > away with a barely noticable degredation due to the other work going on for > that datatype. > > I expect CHAR CI would suffer some minor performance hits because of this > effect, but it couldn't be major. The work involved adding this attribute > in the engine source is not something I'm willing to ponder; perhaps it > would be more cost-effective for Informix to simply release CI versions of > all their locales rather than dicking with the engine too much.

Andrew Hamm wrote: > Lucky: > >>Check this out: >>http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html > > > Yeah, something of that order, but actually the opposite problem! Customers > want to search by name, using "john" to find John john and JOHN. Personally > I would prefer that if a column is declared case insensitive (CI), then > also the strings all compare equal for unique indexing etc. I'm not just > chasing case insensitive comparisons, but storage too. You could in 9.xx/10/11 use the Virtual Index interface to define your own secondary access method to use when creating the index that will automagically convert the inputs. The magic functionality would be supplied in the am_beginscan, am_getnext, and am_rescan functions. The only problem is it will only work if that index is selected. To get it to work for post index comparisons and table scans you'd have to define a primary access method on the table to do the same. Art S. Kagel > Bozon: > >>I wonder if Informix character sets work in a similar way. I looked >>into it but I didn't get anywhere in the past. It looks like you could >>set up a custom character set to do this but then you could have all >>kinds of issues. There has to be at least 1 expert on this on this forum. > > > With NCHAR types? I've been wondering myself if I can create a > case-insensitive English character set and then change our chosen columns > to NCHAR and NVARCHAR, but the more I read about UNICODE and locales, the > more my brain starts to hurt. > > Doug: > >>On 9.4, you can do this: > > > Yes, that idea is floating around here, but then all our SQL's will need to > be modified to use the upshift function in all WHERE clauses. That's a > frightening thought when you have millions of lines of code to deal with. > Even if we did this task, the end-users with Crystal Reports etc will > probably complain that there is no automagic support for them too. > > My understanding is that CHAR types use plain old binary comparisons in the > traditional manner, hence are very fast. > > If you use NCHAR, then all comparisons and matching have to be fed through > locale-sensitive comparison routines, hence they lose a bit of performance. > > If there was a CASE INSENSITIVE clause on the column definitions, then I > expect a CHAR would have to redirect off at least to a case insensitive > comparator function, while an NCHAR CASE INSENSITIVE would probably get > away with a barely noticable degredation due to the other work going on for > that datatype. > > I expect CHAR CI would suffer some minor performance hits because of this > effect, but it couldn't be major. The work involved adding this attribute > in the engine source is not something I'm willing to ponder; perhaps it > would be more cost-effective for Informix to simply release CI versions of > all their locales rather than dicking with the engine too much. >

Art S. Kagel wrote: > You could in 9.xx/10/11 use the Virtual Index interface to define your own > secondary access method to use when creating the index that will > automagically convert the inputs. The magic functionality would be > supplied in the am_beginscan, am_getnext, and am_rescan functions. > > The only problem is it will only work if that index is selected. To get > it to work for post index comparisons and table scans you'd have to define > a primary access method on the table to do the same. Hey bloke, thanks for the reply. How are you keeping? This all sounds interesting, can you go slightly more into the "primary access method" bit for someone who's SQL level hasn't advanced much further than V7, functionality-wise? I think the ultimate goal, unless anyone can make a good point, is: 1) storage as-is, values go in and out in their mixed case without transformation. 2) indexes, comparisons and matches all act caseless, a key test being that john and JOHN cannot both coexist in a unique constraint.

bozon wrote: > My brain hurt too looking at the unicode/locale stuff. I don't think > that the performance is impacted much because the comparison value is > stored in a lookup array. No, not at all for NCHAR types. I believe NCHAR should be doable without any violence to the engine or performance, as long as the comparison routines don't finally fall back to a codepoint comparison when all else fails. From what I've read in Unicode, it seems that in the routines, the depth of comparison can be chosen to any specific level depending on how loose or tight you want the comparison to be. I just think that if plain old CHAR had CI support, then those columns would suffer modest performance drops. Probably minor though. > When I looked at the local source files > ($INFORMIXDIR/gls/lc11/os/en_US.ISO8859-1.lc for example That's more than I have. I have the V10 engine on my laptop - are these files stored in any particular V10 addon pack, maybe the 500Mb multilingual download from the Pardner site? Or do I need to install V11 to start seeing these files? More importantly, how do I compile the .lc files into the working files? > You get definitions like > [SNIP] > Which implies and is really the only sensible way to do it a look up > array. > > they had a character and then a comparison value. So for case > insensitive you had: > > In the collating area you have : > [SNIP] > This also implies that they are building some lookup tables for this > task. So, to make your own case insensitive locale I would change the > weights of the upper and lower case letters to the same values for > example: > \\x41 <Weight_66> # 'A' > \\x61 <Weight_66> #'a' > > > I just haven't been pushed to the point of trying it. I'm being pushed because our BSC's and sales drones are being pushed by customers and prospects. Actually I'm being dragged into it, kicking and screaming, but that's beside the point. I would certainly try it if I could get my hands on a locale compiler.

Andrew Hamm wrote: > Art S. Kagel wrote: > >> You could in 9.xx/10/11 use the Virtual Index interface to define your own >> secondary access method to use when creating the index that will >> automagically convert the inputs. The magic functionality would be >> supplied in the am_beginscan, am_getnext, and am_rescan functions. >> >> The only problem is it will only work if that index is selected. To get >> it to work for post index comparisons and table scans you'd have to define >> a primary access method on the table to do the same. > > Hey bloke, thanks for the reply. How are you keeping? > > This all sounds interesting, can you go slightly more into the "primary > access method" bit for someone who's SQL level hasn't advanced much further > than V7, functionality-wise? > > I think the ultimate goal, unless anyone can make a good point, is: > > 1) storage as-is, values go in and out in their mixed case without > transformation. > > 2) indexes, comparisons and matches all act caseless, a key test being that > john and JOHN cannot both coexist in a unique constraint. > On a real tangent, but one of the previous things I was involved in was to make ALL names "soundexed". Strip out vowels, strip out repeating characters, uppercase everything, and only store a maximum of 8 characters (4 from surname, 2 from forename, and two initials from other names) ... (and probably a few other things). So Forename => "Richard" Surname => "Smith" ends up as "SMTHRCHRD", and then input is "soundexed" and compared against the soundexed name index. This provided a more flexible approach, and some "vague" matching which was very useful for call centres taking calls from anywhere in the world.

TBP wrote: > On a real tangent, but one of the previous things I was involved in was > to make ALL names "soundexed". > [SNIP] > This provided a more flexible approach, and some "vague" matching which > was very useful for call centres taking calls from anywhere in the world. Interesting - how did you achieve this, and was it invisible, in the sense that programmers and Tools users (eg Crystal Reports) did not have to do anything special with the SQL's ?

TBP wrote: > Andrew Hamm wrote: >> Art S. Kagel wrote: >> >>> You could in 9.xx/10/11 use the Virtual Index interface to define >>> your own >>> secondary access method to use when creating the index that will >>> automagically convert the inputs. The magic functionality would be >>> supplied in the am_beginscan, am_getnext, and am_rescan functions. >>> >>> The only problem is it will only work if that index is selected. To get >>> it to work for post index comparisons and table scans you'd have to >>> define >>> a primary access method on the table to do the same. >> >> Hey bloke, thanks for the reply. How are you keeping? >> >> This all sounds interesting, can you go slightly more into the "primary >> access method" bit for someone who's SQL level hasn't advanced much >> further >> than V7, functionality-wise? >> >> I think the ultimate goal, unless anyone can make a good point, is: >> >> 1) storage as-is, values go in and out in their mixed case without >> transformation. >> >> 2) indexes, comparisons and matches all act caseless, a key test being >> that >> john and JOHN cannot both coexist in a unique constraint. >> > > On a real tangent, but one of the previous things I was involved in was > to make ALL names "soundexed". > > Strip out vowels, strip out repeating characters, uppercase everything, > and only store a maximum of 8 characters (4 from surname, 2 from > forename, and two initials from other names) ... (and probably a few > other things). > > So > > Forename => "Richard" > Surname => "Smith" > ends up as "SMTHRCHRD", and then input is "soundexed" and compared > against the soundexed name index. > > This provided a more flexible approach, and some "vague" matching which > was very useful for call centres taking calls from anywhere in the world. There is a developerWorks article exploiting ICU sort keys for DB2 via UDRs. I think it should be easy for anyone who has written a C UDR in the past to adopt it to IDS: http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0602doole/ What this gives you is not only case-insensitive search but also accent insensitive etc, etc... Combined with IDS function indexes the solution should perform nicely. (Maybe Jacques can be convinced to translate the article to IDS speak..) Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab WAIUG Conference http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

Serge Rielau wrote: > There is a developerWorks article exploiting ICU sort keys for DB2 via > UDRs. I think it should be easy for anyone who has written a C UDR in > the past to adopt it to IDS: > http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0602doole/ > What this gives you is not only case-insensitive search but also accent > insensitive etc, etc... As virtual inventors of Unicode, the ICU etc, I'm really surprised that IBM doesn't have comprehensive locale support in DB2. That's really quite surprising. I was under the impression that Informix's formal locales will already do the culturally sensitive comparisons such as the Czech and German examples, or the special reversed ordering of French accent comparisons. Perhaps I should make a few experiments. One of the few customers we have on Microsoft SQL server are now officially "unofficially" allowed to use a CI setup in their database, and they seem to be working happily, so mucking around with MS could be instructive. > Combined with IDS function indexes the solution should perform nicely. > (Maybe Jacques can be convinced to translate the article to IDS speak..) The article does give new interesting information, and hopefully some C code that I can fiddle with to gain hands-on experience with the whole subject. Wish me luck.