Re: Doing case-insensitive searches
On Fri, 27 Nov 1998, Mark D. Stock wrote:
> Oliver Wardman wrote:
> >
> > Art S. Kagel wrote:
> > >
> > > Lisa Retief wrote:
> > > >
> > > > We have just discovered that Informix will only do case-sensitive searches,
> > > > which is a huge problem for a project we are working on. It is possible to
> > > > do a match using syntax such as [A][a] etc, but then the table indexing is
> > > > of no use to us and such searches are far too expensive. Has anyone come
> > > > across a solution for this dilemma? Is there a way to set an Informix
> > > > database to be case-insensitive.
> > > >
> > > > I would appreciate any input. Please respond via email where posible.
> > >
> > > Version 7.30+ can do case insensitive searches and 8.30+ can build case
> > > insensitive indexes as can 9.xx+. If you cannot upgrade do the old
> > > store the data in all upper or lower case in an additional column or
> > > a lookup table.
> > >
> > > Art S. Kagel
> >
> > Can someone tell me how to carry out case insensitive searches as
> > described above? I'm on IDS 7.30.UC3
>
> Try:
> SELECT my_char_col
> FROM my_table
> WHERE LOWER(my_char_col) = "my_search_string_in_lower_case">
Or
SELECT my_char_col
FROM my_table
WHERE LOWER(my_char_col) =
LOWER("My_Search_String_In_Any_Case")
== Olcay Sarioglu
== olcay@knidos.cc.metu.edu.tr http://www.cc.metu.edu.tr/~olcay
== Computer Center, Middle East Tech.Univ. Ankara TURKIYE
== Voice: +90 312 2103381 Fax: +90 312 2101120
> Cheers,
> --
> Mark.
>
> +----------------------------------------------------------+-----------+
> |Mark D. Stock - Informix SA http://www.informix.com |//////// /|
> |mailto:mdstock@informix.com http://www.informix.com/idn |///// / //|
> |http://www.iiug.org +-----------------------------------+//// / ///|
> | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////|
> | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////|
> |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////|
> +----------------------+-----------------------------------+-----------+
>