SUBSTRING: Extracting a Substring From a Source String

How to:

The SUBSTRING function extracts a substring from a source string. If the ending position you specify for the substring is past the end of the source string, the position of the last character of the source string becomes the ending position of the substring.

Top of page
x
Syntax: How to Extract a Substring From a Source String
SUBSTRING(string, position, length)

where:

string

Alphanumeric

Is the string from which to extract the substring. It can be a field, a literal in single quotation marks (‘), or a variable.

position

Positive Integer

Is the starting position of the substring in string.

length

Integer

Is the limit for the length of the substring. The ending position of the substring is calculated as position + length - 1. If the calculated position beyond the end of the source string, the position of the last character of string becomes the ending position.

The data type of the returned substring is AnV.
Example: Extracting a Substring From a Source String

In the following request, POSITION determines the position of the first letter I in LAST_NAME and stores the result in I_IN_NAME. SUBSTRING, 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 = POSITION('I', LAST_NAME); AND
COMPUTE
I_SUBSTR/A3 =
SUBSTRING(LAST_NAME, I_IN_NAME, I_IN_NAME+2);
BY LAST_NAME
ON TABLE SET PAGE NOPAGE
END

The output is:

  LAST_NAME        I_IN_NAME  I_SUBSTR
  ---------        ---------  --------
  BANNING                  5  ING
  BLACKWOOD                0  BL
  CROSS                    0  CR
  GREENSPAN                0  GR
  IRVING                   1  IRV
  JONES                    0  JO
  MCCOY                    0  MC
  MCKNIGHT                 5  IGH
  ROMANS                   0  RO
  SMITH                    3  ITH
                           3  ITH
  STEVENS                  0  ST
WebFOCUS