How can I add case-insensitive conditions to a query when using the LIKE operator?
Background Try different upper, lower, and mixed case values in cell B1 and click Refresh Timberline Data on the Office Connector toolbar.
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%'.
You can force the comparisons to be case-insensitive by forcing both the database value and search-text to upper case. The following step-by-step example illustrates how to do this. In this example, we create a query that provides a list of vendors whose names include the search-text that you enter in cell B1.
In step 4, the formula places wildcard characters at the beginning and ending of the search text. It also utilizes the Excel UPPER function to force the value you enter to upper case.
In step 16, the SQL UCASE function forces the vendor names to upper case. This way, when compared to the value in cell B2, the comparison is not sensitive to case.
Try different upper, lower, and mixed case values in cell B1 and click Refresh Timberline Data on the Office Connector toolbar.