Case Insensitive Query Performance

From: Dean Mah <dmah_at_acs.ucalgary.ca>
Date: Wed, 15 Apr 1998 08:52:30 -0600
Message-ID: <6h2hk8$b1g@ds2.acs.ucalgary.ca>

This must be a FAQ....

I have a table with a surname field which is mixed case. I want to do a case insensitive search against this field. I have used the UPPER function to force both the surname and my search criteria to uppercase. Obviously, this results in poor performance.

I have tried to create an index with UPPER(surname) which didn't work. I've tried to create a view with UPPER(surname) which didn't work. I don't want to create another field with the surname in uppercase because there are >400K records in the table and it is growing.

Any hints, ideas, solutions, ... would be greatly appreciated. Dean Received on Wed Apr 15 1998 - 09:52:30 CDT