PRO_ELEMENT function

Extracts one or more elements from an array column and returns the NVARCHAR or VARCHAR string between the specified positions, including any internal separator characters and any internal escape characters.

Syntax

PRO_ELEMENT ( 'array_style_expression', start_position, end_position ) ;

Parameters

array_style_expression

A string of data type VARCHAR or CHAR, with a semicolon ( ; ) separating each element of the array.

start_position

The position in the string marking the beginning of the element PRO_ELEMENT is to extract.

end_position

The position in the string marking the end of the element to be extracted.

Notes

The array_style_expression must be data type NVARCHAR, VARCHAR, or CHAR.

The returned string does not include the leading separator of the first element, or the trailing separator ( ; ) of the last element.

Even if you are extracting only one element, the escape characters are included in the result.

You must invoke PRO_ARR_DESCAPE to remove any escape characters.

If the expression evaluates to NULL, the result is NULL.

While invoking Pro_Elemen, if Start=End, and the argument is NULL the result is ?.

Example

The following example returns the string 'bb':

PRO_ELEMENT('aa;bb;cc', 2, 2) ;

The next example returns the string 'aa;bb':

PRO_ELEMENT('aa;bb;cc', 1, 2) ;

This example returns the string 'aa~;aa':

PRO_ELEMENT('aa~;aa;bb;cc', 1, 1) ;