EDIT: Extracting or Adding Characters

How to:

Available Languages: reporting

The EDIT function extracts characters from the source string and adds characters to the output string, according to the mask. It can extract a substring from different parts of the source string. It can also insert characters from the source string into an output string. For example, it can extract the first two characters and the last two characters of a string to form a single output string.

EDIT compares the characters in a mask to the characters in a source string. When it encounters a nine (9) in the mask, EDIT copies the corresponding character from the source field to the output string. When it encounters a dollar sign ($) in the mask, EDIT ignores the corresponding character in the source string. When it encounters any other character in the mask, EDIT copies that character to the corresponding position in the output string. This process ends when the mask is exhausted.

Note:

Syntax: How to Extract or Add Characters

EDIT(source_string, 'mask');

where:

source_string

Alphanumeric

Is a character string from which to pick characters. Each 9 in the mask represents one digit, so the size of source_string must be at least as large as the number of 9's in the mask.

mask

Alphanumeric

Is a string of mask characters enclosed in single quotation marks or a field containing the character string enclosed in single quotation marks. The length of the mask, excluding characters other than 9 and $, determines the length of the output field.

Example: Extracting and Adding Characters

EDIT extracts the first initial from the FIRST_NAME field and stores the result in FIRST_INIT. EDIT also adds dashes to the EMP_ID field and stores the result in EMPIDEDIT. The mask used to extract the first initial is stored in the virtual field named MASK1:

DEFINE FILE EMPLOYEE
MASK1/A10 = '9$$$$$$$$$'
END
TABLE FILE EMPLOYEE
PRINT LAST_NAME AND COMPUTE
FIRST_INIT/A1 = EDIT(FIRST_NAME, MASK1);
EMPIDEDIT/A11 = EDIT(EMP_ID, '999-99-9999');
WHERE DEPARTMENT EQ 'MIS';
END

The output is:

LAST_NAME        FIRST_INIT  EMPIDEDIT
---------        ----------  ---------
SMITH            M           112-84-7612
JONES            D           117-59-3129
MCCOY            J           219-98-4371
BLACKWOOD        R           326-17-9357
GREENSPAN        M           543-72-9165
CROSS            B           818-69-2173