How can I add case-insensitive conditions to a query when using the LIKE operator?

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%'.


Solution

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.

1. Start Excel
2. In cell A1, type:  Search Text:
3. In cell B1, type:  electric
4. In cell B2, type:  ="%" & UPPER(B1) & "%"
5. Click Query Wizard on the Office Connector toolbar
6. When prompted, select your Sage 300 Construction and Real Estate database and enter your login credentials.
7. In the Query Wizard window select the AP Vendor table and click Next
8. Check the following fields and click Next
• Vendor
• Name
• Address 1
• Address 2
• City
• State
• ZIP
• Telephone
9. Click Add Condition
10. In the Field box, select Name
11. In the Comparison box, click Like
12. In the Compare to box, select Parameter for the Value Type
13. In the Value box, type:  $B$2
14. Click OK
15. Click Switch to SQL view (next to the View caption)
16. Change the conditions to the following by adding the text highlighted here:  UCASE("VNAME")=$B$2
17. Click Finish
18. Hide row 2.

Try different upper, lower, and mixed case values in cell B1 and click Refresh Timberline Data on the Office Connector toolbar.


Explanation

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.