Case Insensitive Properties

About Case Insensitive Properties

CES supports four different case styles for searching text properties, namely:

  • UPPER (U)

  • LOWER (L)

  • CASE-SENSITIVE (S)

  • CASE-INSENSITIVE (I)

When a text property is marked with UPPER case, the property data is stored as upper case in the database. Likewise, if a property is marked with LOWER case, then the data is stored as lower case. If the case style is either CASE-SENSITIVE or CASE-INSENSITIVE, the property data is stored as is and no case transformation occurs during the data loading.

The case style is defined for each text property in the attribute of S_CLP_CASE_STYLE of the Class Properties class (S_CLP_TABLE). However, when the data of a property is searched, the case style of the property makes a lot of difference. If the case style is UPPER, then the user's search value is converted to upper case and is matched with the database property data, as the following WHERE clause example shows:

WHERE property1 = UPPER (‘searchvalue')

If the case style is LOWER, then the user's search value is converted to lower case and is matched with the database property data, as the following WHERE clause example shows:

WHERE property1 = LOWER (‘searchvalue')

If the case style is CASE-SENSITIVE, then the user's search value is directly matched with the database property data, as shown in the following WHERE clause example:

WHERE property1 = ‘searchvalue'

If the case style is CASE-INSENSITIVE, then both the user's search value and the database property data are converted to UPPER case and matched with each other, as shown in the following WHERE clause example:

WHERE UPPER(property1) = UPPER(‘searchvalue')

The first three cases (UPPER, LOWER and CASE-SENSITIVE) do not pose any additional search performance overheads; however, during a CASE-INSENSITIVE search, the UPPER function is applied to the search property. Oracle turns off use of an index on a property when there is a function, an arithmetic expression, or NULL concatenation added to a property in the WHERE clause, even though there is an index available for the property.

Some of the solutions recommended for the CASE-INSENSITIVE style properties are:

  1. Convert the data to UPPER case and store it in the database.

  2. Train the users so that they know exactly how to enter their search values. For example, they can enter "Software", instead of "software".

  3. For text properties, whose property length is short and which have indexes built for search performance, create a FUNCTION-BASED INDEX (FBI). To do this, from the EDM menu, select Tools > Index Management > Function-Based Index.