x

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

where:

length

Integer

Is the number of characters in the source_string field.

source_string

Alphanumeric

Is the string from which to extract a substring.

start

Integer

Is the starting position of the substring in the source_string. If start is less than 1 or greater than length, the function returns spaces.

end

Integer

Is the ending position of the substring. If end 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 the output_format. Only sublength characters will be processed.

output_format

Alphanumeric

Top of page

Example: Extracting a String

SUBSTR extracts the first three characters from LAST_NAME, and stores the results in a column with the format A3:

SUBSTR(15, LAST_NAME, 1, 3, 3, 'A3')

For BANNING, the result is BAN.

For MCKNIGHT, the result is MCK.