SQL Guide - Expressions.expr-primary-element (Expressions)

Extracts one element from a string of delimiter separated elements

Format

ELEMENT( ElementNo, Delimiter, String, Direction )

{{ FN ELEMENT( ElementNo, Delimiter, String, Direction ) }}

Returns a string containing the specified element.

ElementNo specifies the number of the element to be extracted, 1 for first string, 2 for second and so on.

Delimiter specifies the string used to separate elements in the string, all characters are used.

String specifies a string containing a delimited list of elements.

Direction specifies the order to search in, 0 for ElementNo to apply from the left otherwise applies from the right.

If the ElementNo is greater than the number of elements in the string then the delimiter is returned.

Both Delimiter and String can be any string expression.

Example:

Example 1: Return the first element of a comma delimited string.

ELEMENT( 1, ',', 'Good,Bad,Failed', 0 )

Example 2: Return the last element of a comma delimited string using ODBC syntax.

{FN ELEMENT( 1, ',', 'Good,Bad,Failed', 1 )}