Case Insesitive search (how)

Hello all, How do I do a case insesitive search. Lets say I have a field "This Is A Test", I would like to seach for it using "this is a", (SELECT * FROM table WHERE field LIKE '%this is a%' ) does not work. Any ideas? Richard Krenek

Richard Krenek wrote: > > Hello all, > How do I do a case insesitive search. Lets say I have a field "This > Is A Test", I would like to seach for it using "this is a", (SELECT * > FROM table WHERE field LIKE '%this is a%' ) does not work. Any ideas? If you have IDS 7.3x or higher you can use the LOWER and/or UPPER functions: SELECT * FROM table WHERE lower(field) LIKE '%this is a%' ...; But it will not be able to use any index on 'field'. If you have 8.2x or higher you can use functional indexes to index the 'field' using the LOWER or UPPER function results and the engine will use it for: SELECT * FROM table WHERE upper(field) LIKE upper('%this is a%') ...; If you have IDS.2000/IIF.2000 you can create a UDI (User Defined Index) to accomplish this search. Art S. Kagel

One option to consider is the advanced indexing technology of OMNIDEX from DISC (Dynamic Information Systems Corporation). (Attention: Promotional information follows. Please disregard if not interested in another solution.) OMNIDEX uses specialized Multidimensional Keyword Indexes to deliver case-insensitive full text searches by any number of criteria or columns. OMNIDEX layers on top of your existing database, and it supports numerous databases and document files, including Informix, Oracle, SQL Server, Sybase, flat files, HTML and Word documents. OMNIDEX is ideal for ad-hoc querying, it works well for both high and low cardinality data, and is very efficient in terms of build time and disk space. How much OMNIDEX can help depends on your needs and environment. If you would be interested in a free performance analysis or more information, please contact me. Cheryl Grandy DISC cgrandy@disc.com 303 444-4000 www.disc.com/home OMNIDEX - for the fastest applications ever! In article <392AC8BC.C3649C7B@ihs.com>, Richard Krenek <rkrenek@ihs.com> wrote: > Hello all, > How do I do a case insesitive search. Lets say I have a field "This > Is A Test", I would like to seach for it using "this is a", (SELECT * > FROM table WHERE field LIKE '%this is a%' ) does not work. Any ideas? > > Richard Krenek > > -- Cheryl Grandy DISC Get OMNIDEX for the fastest applications ever Sent via Deja.com http://www.deja.com/ Before you buy.

oh but..... if you use 9.14 you're out of luck because Informix *forgot to put them in*. Strange but true, 'upper' and 'lower' didn't make it into 9.14. They are back in for 9.2 apparantly...Along with a substring function for allowing variable indexing into a string. For 9.14, there is an external C substring function in the IIUG repository (look under 'split') but I haven't got around to compiling it in yet. It would be pretty easy to create a C function to upper and lower using that as a template. Andrew. Art S. Kagel wrote in message <392AD02A.92B3A4A0@bloomberg.net>... >Richard Krenek wrote: >> >> Hello all, >> How do I do a case insesitive search. Lets say I have a field "This >> Is A Test", I would like to seach for it using "this is a", (SELECT * >> FROM table WHERE field LIKE '%this is a%' ) does not work. Any ideas? > >If you have IDS 7.3x or higher you can use the LOWER and/or UPPER functions: > >SELECT * >FROM table >WHERE lower(field) LIKE '%this is a%' ...;> >But it will not be able to use any index on 'field'. If you have 8.2x or >higher you can use functional indexes to index the 'field' using the LOWER >or UPPER function results and the engine will use it for: > >SELECT * >FROM table >WHERE upper(field) LIKE upper('%this is a%') ...;> >If you have IDS.2000/IIF.2000 you can create a UDI (User Defined Index) to >accomplish this search. > >Art S. Kagel