EDIT: Extracting or Adding Characters

Available Operating Systems: All Available Languages: reporting

How to: Extract or Add Characters

Example: Extracting and Adding a Character to a Field

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

EDIT works by comparing the characters in a mask to the characters in a source field. When it encounters a nine in the mask, EDIT copies the corresponding character from the source field to the new field. When it encounters a dollar sign in the mask, EDIT ignores the corresponding character in the source field. When it encounters any other character in the mask, EDIT copies that character to the corresponding position in the new field.

EDIT can also convert the format of a field. For more information on converting a field with EDIT, see EDIT: Converting the Format of a Field.

Syntax: How to Extract or Add Characters

EDIT(fieldname, 'mask');

where:

fieldname

Alphanumeric

Is the source field.

mask

Alphanumeric

Is a character string enclosed in single quotation marks.

The length of the mask, excluding any characters other than nine and $, should be the length of the source field.

Example: Extracting and Adding a Character to a Field

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:

TABLE FILE EMPLOYEE
PRINT LAST_NAME AND COMPUTE
FIRST_INIT/A1 = EDIT(FIRST_NAME, '9$$$$$$$$$');
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