Question: I need to convert my field values to upper (or lower) case. How can I do this quickly?

Answer: You can do this with a simple SQL query.

To demonstrate this, let's assume you have a Contacts table containing addresses in North America. For some reason, your State and Province abbreviations have been entered in mixed case. For example, some of your California contacts have been entered as "ca", "Ca, and "CA." The following SQL query would change these to "CA" and all other abbreviations to upper case:

UPDATE
   "CONTACTS.DB"
SET
   StateProv = upper( StateProv )

Entering and Executing a SQL query

If you haven't used SQL queries before, the following process walks you through the steps needed to enter an execute the above query in Paradox 9:

1. Start by choosing File | New | SQL File.

2. When the New SQL dialog appears, choose Blank Editor.

3. when the Editor appears, enter the above query or paste in from the Clipboard.

4. Press <F8> or choose the SQL | run SQL menu command.

When the query is finished, note that Paradox shows you a table called Updated. This contains copies of the records that were changed by the query, however, these are not the current values. Instead, this is a backup table containing the original field values, e.g. the values that were updated by the query. To view the modified data, open Address in a separate table window.

Converting to Lower Case

The above query uses a SQL function called upper(), which returns the upper case versions of the underlying field values.

To convert values to lower case, use the lower() function instead.

If you need to convert field values, such as country names, job titles, and so on, to mixed case, use a script. An example is provided in the Converting Values to Mixed Case script tutorial.

Also, if you find yourself needing this sort of conversion on a regular basis, you may wish force the data to upper case when it's entered by the user. For more information, see Saving Data in Upper (or Lower) Case.