Available Languages: reporting
The SUBSTR function extracts a substring based on where it begins and its length in the source string. SUBSTR can vary the position of the substring depending on the values of other fields.
There is a version of the SUBSTR function that is available only in the Maintain language. For information on this function, see SUBSTR: Extracting a Substring (Maintain).
SUBSTR(length, source_string, start, end, sublength, output)
Is the number of characters in source_string, or a field that contains the length.
Is the string from which to extract a substring enclosed in single quotation marks, or the field containing the parent string.
Is the starting position of the substring in the source string. If start is less than one or greater than length, the function returns spaces.
Is the ending position of the substring. If this argument is less than start or greater than length, the function returns spaces.
Is the number of characters in the substring (normally end - start + 1). If sublength is longer than end - start +1, the substring is padded with trailing spaces. If it is shorter, the substring is truncated. This value should be the declared length of output. Only sublength characters will be processed.
Is the field to which the result is returned, or the format of the output value enclosed in single quotation marks.
POSIT determines the position of the first letter I in LAST_NAME and stores the result in I_IN_NAME. SUBSTR then extracts three characters beginning with the letter I from LAST_NAME, and stores the results in I_SUBSTR.
TABLE FILE EMPLOYEE PRINT COMPUTE I_IN_NAME/I2 = POSIT(LAST_NAME, 15, 'I', 1, 'I2'); AND COMPUTE I_SUBSTR/A3 = SUBSTR(15, LAST_NAME, I_IN_NAME, I_IN_NAME+2, 3, I_SUBSTR); BY LAST_NAME WHERE DEPARTMENT EQ 'PRODUCTION' END
The output is:
LAST_NAME I_IN_NAME I_SUBSTR --------- --------- -------- BANNING 5 ING IRVING 1 IRV MCKNIGHT 5 IGH ROMANS 0 SMITH 3 ITH STEVENS 0
Since Romans and Stevens have no I in their names, SUBSTR extracts a blank string.