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 numeric position in the string marking the beginning of the element PRO_ELEMENT is to extract.

end_position

The numeric 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 when 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.
• When accessing a sequence of multiple array elements if all the chosen array elements are NULL, then, the value of PRO_ELEMENT() is NULL. As any expression involving NULL evaluates to NULL, you should be careful when using the PRO_ELEMENT in this way when all the referenced array elements may be NULL. As an example, for a column named "col1" is an array of five elements of Date datatype, where, col1[2]..col5] are NULL, then, the value of PRO_ELEMENT("col1", 2, 5) is NULL.
• When the Authorized Data Truncation (ADT) is set to ON or OUTPUT , using the PRO_ELEMENT for var array might return the error “invalid array element reference”. To understand this better, refer Example 2 of this topic.
• If an application wants to obtain a sequence of '?' values for a sequence of array elements each equal to NULL, use an expression combining the individual array elements. For example:

  PRO_ELEMENT("col1",2,2) || ';' || PRO_ELEMENT("col1",3,3) || ';' || PRO_ELEMENT("col1",4,4) || ';' || PRO_ELEMENT("col1",5,5)

• While invoking PRO_ELEMENT, if Start=End, and the argument is NULL the result is (that is, the referenced array element is NULL) the result is '?'. For example, in the following function call, if sales_history[2] is NULL, then the PRO_ELEMENT returns ‘?’.

  PRO_ELEMENT(sales_history, 2, 2)

Example 1

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

Example 2

Consider a var array ( cVar VARCHAR(5) VARARRAY[2]) into which ABL users insert the data (‘123456789012345’;’1234567890’).

Now, SQL users execute the SQL query select pro_elemenet( cvar,2,2) from table when the Authorized Data Truncation (ADT) is set to on/output. The expected output with the current SQL behavior is to throw the error : invalid array element reference.

The reason for this behavior is that in case of a var array, while converting it into to string format, the truncation should happen on the entire array rather than on each element. The VARARRAY data type allows the size of an individual element value to exceed its declared size as long as the total size of the array is smaller than the array's SQL width.

So, for a var array, each element will be read incrementally until the entire data for the element is read. While doing so, if the total array length is exhausted, further data will not be read and the string produced so far will be returned.

In the above example, the first element is read first even though the second element is required and the array length is exhausted. So, the returned string does not have a reference to second element and it returns invalid array element reference.

This is an expected behavior. However, to get the second element, SQL users can and should use the subscript notation: my_array[2].