How can I apply conditions that are not case-sensitive when using TSCount or TSSum with a LIKE operator?
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%'
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.
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.