Problem: How to get the search criteria in the WHERE clause to ignore case sensitivity Solution: Option 1: WHERE "WORD" = UPPER(field1) or WHERE UPPER(:variable) = UPPER(field1) Disadvantage: This method complicates coding and disallows indexing or sorting based on the uppercase version. Option 2: WHERE field1 CONTAINING "WORD" Disadvantage: This method does not seem to work for international character sets and disallows indexing or sorting based on the uppercase version. Option 3: Create another column for each text column you want to search in a case insensitive manner. Make a trigger on INSERT and UPDATE to force this column to have an uppercase equivalent string. Example: CREATE TABLE foo ( field1 CHAR(10), field1_UC CHAR(10)); Trigger Code: SET TERM !!; CREATE TRIGGER foo_UC FOR foo BEFORE INSERT AS BEGIN new.field1_uc = UPPER(new.field1); END!! SET TERM ;!! Create an index on field1_UC and perform searches and sorts based on that column. Disadvantage: This method doubles the storage requirements for the field, having one for the original data and one for the uppercased data. Option 4: You can create a computed column to display the uppercase string: Example: CREATE TABLE foo ( field1 CHAR(10), field1_UC COMPUTED BY (UPPER(field1))); NOTE: This method does not store a string for field1_UC in each row. It computes the uppercase string at runtime. Disadvantage: It disallows sorting or indexing based on uppercase version.
Last Modified: 29-SEP-00