Search code examples
foxpro

Foxpro SELECT query cant find item is case isnt correct

I have a table with a CustomerName field. I have a record where the CustomerName is 'CustomerABC' my select query is as follows:

   SELECT fieldA, fieldB, fieldC
from CustomersTable
WHERE CustomerName='CustomerABC'

This returns the record but if the case is wrong, it returns nothing. e.g.

    SELECT fieldA, fieldB, fieldC
from CustomersTable
WHERE CustomerName='customerabc'

would produce no results.

Surely, users aren't expected to know the correct case of the record they are searching for!? Could someone help me with this please?

Thanks John

Solution

  • By default FoxPro's = and == operators are case sensitive so this will impact queries. However, the command SET COLLATE TO "GENERAL" can be used to change the behavior and will allow upper/lowercase characters to match in comparison operations.

    If you want to use the default and ignore case, then use either UPPER() or LOWER() functions to convert the strings for comparison. If the query will be executed in code (vs an ad hoc query in the command window) it is usually a good idea to apply the functions to both sides of the comparison unless you know for sure that the data in the table is formatted a certain way.

    SELECT fieldA, fieldB, fieldC
from CustomersTable
WHERE UPPER(CustomerName)=UPPER('customerabc')

    Also when using string comparisons, consider the impact of the SET EXACT and SET ANSI settings as they control the behavior for padded strings and strings of different length.