Extracting Part of the Text From an Alphanumeric Field

When data is in an alphanumeric field, you can extract some of the characters from the data or change the way the characters are presented. For example, you might want to pick out the first character of the job code and use it as the job category.

You can use the EDIT function to do this. Using EDIT, you can:

• Change the way an alphanumeric field displays.
• Extract part of a field.
• Change an alphanumeric field to an integer field or an integer field to an alphanumeric field.

To change an alphanumeric field to an integer field, the basic format is:

field_alpha/An=EDIT(field_numeric);

To use the EDIT function to extract characters, the basic format is:

field/An= EDIT(fieldname,'mask');

In this structure, field is the name you give the temporary field. You must use A to declare that the temporary field is alphanumeric. The n represents an integer you supply that defines the field length.

The fieldname is the name of the field in the data source, and mask represents a series of characters you supply that act as a filter for the data found in the field. Enclose your masks within single quotation marks (').

The mask works like this:

• If a character in the mask is a "9", then the character in the same position in a data value is passed to the new field.
• If a character in the mask is a "$", then the character in the same position in a data value is discarded.
• Any other character is simply inserted in the field and the other characters are moved over one position to the right.

For example, CURR_JOBCODE contains a three-character job code consisting of a letter and two numbers. In the next example, you use EDIT to discard the letter and extract the two numbers. You use them in a field called JOB_LEVEL.

Issue the following DEFINE command:

DEFINE FILE EMPLOYEE ADD
JOB_LEVEL/A2=EDIT(CURR_JOBCODE, '$99');
END

Although you see nothing on your screen when you execute this or any DEFINE command, this is what it does once you execute a report request.

The $ in the first position in the mask discards the letter from each data value retrieved from CURR_JOBCODE. The nines (9) in the next two positions pass the two numbers retrieved from each value in CURR_JOBCODE to the temporary field JOB_LEVEL.

Now, issue the following request:

TABLE FILE EMPLOYEE
SUM CURR_SAL BY JOB_LEVEL
END

Run the request. The output is:

This example illustrates the basics of the EDIT function.