ToolBox -

Background

The LIKE operator is useful for identifying rows in a table by matching on a partial value.  For example, the following expression would identify vendors with names containing the word “Electric” (referred to as the search-text in this article):

[VNAME] LIKE ‘%Electric%’

Problem

Comparisons using the LIKE operator are case-sensitive and so you may not receive the desired results depending on the use of upper or lower case letters in your search-text.  For example, the search text ‘%electric%’ would return different results than ‘%Electric%’.

Special Note – Advanced users may use an SQL statement as the table-name argument in order to improve performance with large data-sets.  When this is done, the conditions specified in the filter argument are processed locally and are not case-sensitive.

Solution

When using the LIKE operator with TSCOUNT or TSUM, you can force the comparisons to be case-insensitive by forcing both the database value and search-text to upper case in your filter argument.

Here is an ordinary TSCOUNT function that uses the LIKE operator to count the number of AP Vendor records having a name that contains the search-text entered in cell B1.  This function would be case-sensitive:

=TSCOUNT(“MASTER_APM_RECORD_9″,”[VNAME] LIKE ‘%” & B1 & “%’”)

Both sides of the comparison can be forced to upper-case during the comparison so that the result will no longer be case-sensitive.  This is done using the SQL UCASE function with the column name, and Excel’s UPPER function with the search-text as shown here:

=TSCOUNT(“MASTER_APM_RECORD_9″,”UCASE([VNAME]) LIKE ‘%” & UPPER(B1) & “%’”)

Note that the parts of the formula that were changed are highlighted.