LOWER(), PROPER(), UPPER()

It’s a funny thing. These three functions do very similar things, yet UPPER() and LOWER() are among the most important functions in FoxPro, while PROPER() is one of the most useless. All three take a character string and return a string with the case of the letters modified—UPPER() converts them all to uppercase, LOWER() makes them all lowercase, and PROPER() supposedly makes proper names out of them (with a capital letter at the beginning of each word).

Usage

cResult = LOWER( cString )
cResult = PROPER( cString )
cResult = UPPER( cString )

In many comparisons and searches, using either UPPER() or LOWER() is essential, so the operation is case-insensitive. You don’t want to worry about whether the user has entered “Smith”, “smith”, “SMITH”, or “sMiTH”. So you apply UPPER() or LOWER() to both sides of the comparison or to both the search string and the target of the search. For example, if a user has filled in variables cLastName and cFirstName, you could search for a matching record like this:

LOCATE FOR UPPER(LastName) = UPPER(cLastName) AND ;
           UPPER(FirstName) = UPPER(cFirstName)

The same logic applies when creating indexes based on character strings. If case is irrelevant (which is true more often than not), apply UPPER() to the data being indexed and then always be sure to SEEK using UPPER(the search string). For example, to re-create the company name index for TasTrade’s Customer table, you use:

INDEX ON UPPER(Company_Name) TAG Company_Na

Then, to search for QUICK-Stop, you’d:

SET ORDER TO TAG Company_na
SEEK "QUICK-STOP"

To search for the company whose name is contained in m.cCompany, use:

SEEK UPPER(m.cCompany)

Actually, if m.cCompany comes from user input, you should probably trim it first to get rid of any leading or trailing blanks that might have crept in:

SEEK UPPER(ALLTRIM(m.cCompany))

You can use LOWER() exactly the same way as UPPER() in searches and comparisons. Just pick one or the other and always use it, so you know what to expect. Harking back to our mainframe days, we tend to use UPPER(), but LOWER() is more readable, the ergonomists tell us; we just haven’t lowered ourselves to using LOWER() yet.

PROPER() is one of those great ideas that just didn’t work out. Its purpose is to let folks enter names without worrying about capitalization. Then, you can come along later and apply PROPER() to fix it up. Unfortunately, names just aren’t that simple. While PROPER() is great for run-of-the-mill names (like Granor or Roche), it falls apart when you hand it stuff like O’Hara or MacNeill.

Fundamentally, PROPER() is too simple-minded to do the job. It takes whatever you hand it and returns it with the first character of each word capitalized. If that’s not appropriate, too bad. Fortunately, there are plenty of smart Fox programmers out there. Look for alternatives in the usual places. (For a list of the usual places, see “Resource File” in “Back o’ da Book.”)

See Also

IsLower(), IsUpper()