SUBSTR: Extracting a Substring

How to:

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).

Syntax: How to Extract a Substring 

SUBSTR(length, source_string, start, end, sublength, output)

where:

length

Integer

Is the number of characters in source_string, or a field that contains the length.

source_string

Alphanumeric

Is the string from which to extract a substring enclosed in single quotation marks, or the field containing the parent string.

start

Integer

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.

end

Integer

Is the ending position of the substring. If this argument is less than start or greater than length, the function returns spaces.

sublength

Integer

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.

output

Alphanumeric

Is the field to which the result is returned, or the format of the output value enclosed in single quotation marks.

Example: Extracting a String

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.