Case-Insensitive searches in BIOVIA Integrating Data Sources

How can I make my searches case-insensitive in my BIOVIA Integrating Data Source (IDS)?

The information below is from the section “Case-Insensitive Searching with Oracle Data Sources” in the BIOVIA Query Service Integrating Data Source Guide.

Case-Insensitive Searching with Oracle Data Sources

By default, string comparisons are case-sensitive in Oracle. This behavior carries through to the Query
Service. Therefore, if you search on a string in an Oracle-based IDS, only exact case-sensitive matches are returned.

You can override this behavior for a particular output field by defining case-insensitive string
searching. Do this as follows:

  1. Define the output field's name attribute using the upper SQL function. This converts the          value in the database to uppercase.
  2. Include an alias attribute. This is recommended for all output fields that are functions.
  3. Include an isFunction="true" attribute.
  4. Include a searchupper="true" attribute. This converts strings in users' search criteria to          uppercase, so that they match the uppercase values retrieved from the database.

Notes:

  • If the values in the database are already consistently in uppercase, only Point 4 above is       required.
  • Pivot connectors do not support functions as names, and therefore do not support case         insensitive searching, because it relies on the upper function. (An exception to this                 statement is when the database values are always in uppercase — see the preceding           bullet point.)
  • By default, case-insensitive searching is slower than case-sensitive searching. However,       you can optimize performance of case-insensitive searching by indexing upper({field             name}) in your database.
  • You can also use the steps above to implement case-insensitive searching of a case-             sensitive SQL Server database. Note that string comparisons are case-insensitive in             SQL Server by default.

Example
This example output field specifies case-insensitive searching on the CHEMICAL_NAME database field, by converting string data retrieved from the CHEMICAL_NAME field, and users' search terms, to uppercase.
                        isFunction="true" type="VariableText"
            display_name="Chemical Name" searchupper="true" />

There are additional examples of using 'searchupper' in most BIOVIA Content database IDS xml files, such as ACD_IDS.xml, TOX_IDS.xml and others.

The BIOVIA Query Service Integrating Data Source Guide also has information on the following, in the same chapter:

* Retaining the Original Case in Results
* Making Case-Sensitivity Optional