Re: How can I do a case insensitive search?
fschuppenhauer@my-dejanews.com wrote:
>
> How can I do a case insenitive search in a SQL-Statement, I mean,
> somethin like this
>
> SELECT foo
> FROM anicetable
> WHERE string1 = strInG2;
...where string1 matches "[Ss][Tt][Rr][Ii][Nn][Gg]2" :-)
--
/* ----------------------------------------------------- *
* Willem Roos wroos@shoprite.co.za *
* roosj@mweb.co.za *
* 0(+27)21 980 4941 *
* 0(+27)21 919 0198 *
* ----------------------------------------------------- */
Willem Roos wrote:
>
> fschuppenhauer@my-dejanews.com wrote:
> >
> > How can I do a case insenitive search in a SQL-Statement, I mean,
> > somethin like this
> >
> > SELECT foo
> > FROM anicetable
> > WHERE string1 = strInG2;>
> ...where string1 matches "[Ss][Tt][Rr][Ii][Nn][Gg]2" :-)
The problem is that this version will not use any index on the column...
Depending on your data that can be unacceptable...
The solution I used is to hold an extra column with UPPERCASE letters
and to search with an UPPERCASE string.
Of course this is not the way it should go, but I think there is no
better way in Informix at this time.
Achim
Achim Reiners wrote:
> Willem Roos wrote:
> >
> > fschuppenhauer@my-dejanews.com wrote:
> > >
> > > How can I do a case insenitive search in a SQL-Statement, I mean,
> > > somethin like this
> > >
> > > SELECT foo
> > > FROM anicetable
> > > WHERE string1 = strInG2;> >
> > ...where string1 matches "[Ss][Tt][Rr][Ii][Nn][Gg]2" :-)
>
> The problem is that this version will not use any index on the column...
> Depending on your data that can be unacceptable...
>
> The solution I used is to hold an extra column with UPPERCASE letters
> and to search with an UPPERCASE string.
> Of course this is not the way it should go, but I think there is no
> better way in Informix at this time.
>
> Achim
No, the best way is to use 9.x and use a user-defined function UPPER()
(unless they've added UPPER to 9.whatever) and then create an index on
UPPER(column). That will use the index and not force you to store the data
twice. But if you're not on 9.x, then you have a few choices (which have
been discussed already), all sub-optimal, of which this extra column is,
IMHO, the best.
June
--
june_t@hotmail.com
Grounded in Palo Alto, living on M&M's (plain)