case sensitivity in query

Hello, I use Informix 7.23 on HP-UX 10.20. My problem is the following : I have a column which is case sensitive for example table A col --- aaa Aab aBC I need to perform some query on this column with no sensitivity. For example, col start with aa will give 'aaa' and 'Aab'. I'm very surprised but I've found no way to do this with Informix (if I can remember, it was very easy with Oracle or Sybase for example). Any idea ? Thanks in advance. mailto:pap@sablet.grenoble.hp.com

Philippe Arnod-Prin wrote: > > Hello, > I use Informix 7.23 on HP-UX 10.20. > My problem is the following : > I have a column which is case sensitive > for example > table A > col > --- > aaa > Aab > aBC > I need to perform some query on this column with no sensitivity. > For example, col start with aa will give 'aaa' and 'Aab'. Try this: SELECT bla FROM bla WHERE col MATCHES "[Aa][Aa]*" > > I'm very surprised but I've found no way to do this with Informix > (if I can remember, it was very easy with Oracle or Sybase for example). > Any idea ? > Thanks in advance. > > mailto:pap@sablet.grenoble.hp.com Markus

Philippe Arnod-Prin wrote: > > Hello, > I use Informix 7.23 on HP-UX 10.20. > My problem is the following : > I have a column which is case sensitive > for example > table A > col > --- > aaa > Aab > aBC > I need to perform some query on this column with no sensitivity. > For example, col start with aa will give 'aaa' and 'Aab'. > > I'm very surprised but I've found no way to do this with Informix > (if I can remember, it was very easy with Oracle or Sybase for example). This is a recurring theme. In 7.30 there are UPPER and LOWER functions built in that can help but they will not use any indexes only table scan or low level filter. There was posted a stored procedure to emulate the UPPER function but it is slow. You can use the MATCHES clause's wild cards: WHERE col MATCHES "[aA][bB][cC]" but that is rather ugly and will also not use indexes. IDS/XPO (ie 8.xx) has what are called functional indexes which can create an index based on the result of an arbitrary function or SPL and you can implement something like this in 9.xx (IDS/UDO) using DataBlades to define new indexing types. Hopefully when the three code bases merge later this year this great 8.xx feature will be available for base IDS also. But for true blue 7.xx the truth is the only good solution is to add another column with the mixed case column in either lower or upper case and index it. Then you can just map any search string for the column to upper or lower and query against the single case lookup column. Note that Sybase and Oracle do the same thing that 7.3x does they supply an UPPER and LOWER function and cannot utilize indexes so they are really no better. Art S. Kagel