Case Insensitive searching

I notice that in IDS 7.3 we have a function LOWER is assist in Case Insensitive searching. So that means that I can do SELECT lastname from users WHERE LOWER(lastname) = "mckay" But this turns out to be pretty useless because now the index I had placed on lastname is not utilized. So now I'm back to SELECT lastname from users WHERE lastnameLC = "mckay" lastnameLC being a duplicate column in the table with the lowercase value of lastname. Am I missing something?, is there some sort of function like CREATE INDEX idx_lastname on users (lastname LOWER) ?? Any help appreciated, Chris (Remove nospam from email address if replying via that format)

The exact functionality you are looking for is in IDS 9.2. You have the ability to build an index on the results of a function. I believe the syntax is (off the top of my head): CREATE INDEX i_users1 ON users(LOWER(lastname)); When performing a search against LOWER(lastname), the optimizer will now use the function index. Troy Chris McKay <chris_mckay@nospam-hotmail.com> wrote in message news:7q028v$sgc$1@m2.c2.telstra-mm.net.au... > I notice that in IDS 7.3 we have a function LOWER is assist in Case > Insensitive searching. So that means that I can do > SELECT lastname from users WHERE LOWER(lastname) = "mckay"> > But this turns out to be pretty useless because now the index I had placed > on lastname is not utilized. So now I'm back to > > SELECT lastname from users WHERE lastnameLC = "mckay"> > lastnameLC being a duplicate column in the table with the lowercase value of > lastname. Am I missing something?, is there some sort of function like > > CREATE INDEX idx_lastname on users (lastname LOWER) ??> > Any help appreciated, > Chris > (Remove nospam from email address if replying via that format) > >

Chris McKay wrote: > > I notice that in IDS 7.3 we have a function LOWER is assist in Case > Insensitive searching. So that means that I can do > SELECT lastname from users WHERE LOWER(lastname) = "mckay"> > But this turns out to be pretty useless because now the index I had > placed on lastname is not utilized. Design a query plan which works with the index? Actually, in this case, you could do a reasonable job because the range is constrained to 'M' or 'm', two disjoint ranges in the same index. However, that requires a good deal more knowledge of what the LOWER function does than is probably available to the optimizer. And other cases are not so easy. I have sympathy with the optimizer folks for not being able to use an index, however much I'd like it to be able to do so. > So now I'm back to > > SELECT lastname from users WHERE lastnameLC = "mckay"> > lastnameLC being a duplicate column in the table with the lowercase > value of lastname. Am I missing something?, is there some sort of > function like > > CREATE INDEX idx_lastname on users (lastname LOWER) ?? Ah; you want a functional index, which AFAIK is available in Foundation.2000 and it's version 9.x predecessors (IDS/UDO and IUS). -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

Chris - This might not help much, but here's what I'd do (short of using IDS 9.2): 1. Upshift the last name *before* adding the record, and upshift the last name on all existing records as well. 2. Create an index on the upshifted column. 3. Upshift the input string (I assume from the Web or a GUI) before using it in the WHERE clause. That solves your search problem, though now you have *only* upshifted values in the database. If you need to *display* in mixed case, for example in a UI or a report, you can: 3. Maintain a redundant column which stores the last name in mixed case, and use that column only when displaying this field. As a *far* better solution, I've written very useful downshift-to-mixed-case algorithms, which don't require any database storage space (obviously), and are quite fast. It could even handle some exceptions, such as "O'Connor" or "McEnroe". In fact, simply upshifting the 3rd character if the second is an apostrophe or if the first two are "MC" works surprisingly well in well over 99.9% of the cases - and not too badly in the other 0.5%. Rich Chris McKay wrote: > I notice that in IDS 7.3 we have a function LOWER is assist in Case > Insensitive searching. So that means that I can do > SELECT lastname from users WHERE LOWER(lastname) = "mckay"> > But this turns out to be pretty useless because now the index I had placed > on lastname is not utilized. So now I'm back to > > SELECT lastname from users WHERE lastnameLC = "mckay"> > lastnameLC being a duplicate column in the table with the lowercase value of > lastname. Am I missing something?, is there some sort of function like > > CREATE INDEX idx_lastname on users (lastname LOWER) ??> > Any help appreciated, > Chris > (Remove nospam from email address if replying via that format) -- Richard C. Auslander Database Manager AirFlash, Inc. 1733 Woodside Rd., Suite #110 Redwood City, CA 94061 (650) 556-7928 www.airflash.com