community.borland.com

Article #25171: Case (insensitive) record searching methods

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