---

Return to the DDS Tips

---

Indexing mixed case fields

By Tim Granatir, search400.com member If you've ever worked with a field that contains a mixture of capital and lowercase letters, such as a name or address field, you have no doubt noticed that it can be difficult to correctly sort on these fields. Because of their binary representation in EBCDIC, lowercase letters are sorted before uppercase letters. For example, say you had a field called Address and in three separate records the Address field contained the following data: "aa" in record 1, "AB" in record 2, and "bb" in record 3. If you tried to sort on these fields, the records would be sorted as "aa", "bb" and then "AB".

To get these records to sort properly, you can add a field to your logical file using the RENAME function and use an AS/400-supplied translation table to convert the data in this new field to uppercase. An example of this follows:

A          R ILCENHFM                  PFILE(ILCENH)      
A            ENHMIX                                       
A            ENHCAP             I      RENAME(ENHMIX)     
A                                      TRNTBL(QSYSTRNTBL) 
A          K ENHCAP    

By keying this file on the newly created and now all uppercase ENHCAP field, your records will now be presented in the correct sequence and you will still have access to original field that contains mixed case characters.

[report a broken link by clicking here]

---