SUBSTV: Extracting a Variable Length Substring

How to:

The SUBSTV function extracts a substring from a string and is similar to SUBSTR. However, the end position for the string is calculated from the starting position and the substring length. Therefore, it has fewer parameters than SUBSTR. Also, the actual length of the output field, if it is an AnV field, is determined based on the substring length.


Top of page

x
Syntax: How to Extract a Variable Length Substring
SUBSTV(upper_limit, source_string, start, sub_limit, output)

where:

upper_limit

Integer

Is the limit for the length of the source string.

source_string

Alphanumeric of type An or AnV

Is the character string that contains the substring you want to extract. If it is a field of type AnV, its length is taken from the length bytes stored in the field. If upper_limit is smaller than the actual length, the source string is truncated to the upper limit. The final length value determined by this comparison is referred to as p_length (see the description of the output parameter for related information).

start

Integer

Is the starting position of the substring in the source string. The starting position can exceed the source string length, which results in spaces being returned.

sub_limit

Integer

Is the length, in characters, of the substring (normally end - start + 1). The end position of the substring is end =start + sublength -1. Note that the ending position can exceed the input string length depending on the provided values for start and sub_limit.

output

Alphanumeric of type An or AnV

If the format of output is AnV, the actual length, outlen, is computed as follows from the values for end, start, and p_length (see the source_string parameter for related information):

If end > p_length or end < start, then outlen = 0. Otherwise, outlen = end - start + 1.



Example: Extracting a Variable Length Substring

SUBSTV extracts the first three characters from the TITLEV and stores the result in a column with the format A20V:

SUBSTV(39, TITLEV, 1, 3, 'A20V')

For SMURFS, the result is SMU.

For SHAGGY DOG, the result is SHA.


iWay Software