Re: Case Insensitive Index (was Re: Three cheers for 4gl's CONSTRUCT)

The problem with all these MATCHES "[Ss][Mm][Ii][Tt][Hh]" solutions to mixed case queries is that the engine will typically not use any indexes, so it's only really useful on small tables. I believe I have seen OnLine 5.01 say it was using an index on a simple query, but I may have been halluncinating. It certainly does not on any moderately complex query. The task of combining multiple index paths to do the right thing is a little complex, so maybe we shouldn't expect SQL engines to be able to do it :-). I don't believe Sybase ot Oracle use indexes in this case either. That being the case, one would really like the database engine to provide support for case insensitive indexes (where it looks after normalizing the index strings and lookups). This can certainly be done in the application with extra fields and application code to select on the other field appropriately, but this is work, tricky to do in many front-end tools, and not scalable to many apps all talking to the same database (they all have to understand what to do). -- --------------------------------------------------------------------------- Lawrence Byrd Internet: lawrence@quintus.com Quintus Corporation Phone: USA 415 254 2805 An Intergraph Company 301 East Evelyn Avenue, Mountain View, California 94041 ---------------------------------------------------------------------------