$LISTGET (SQL)

A list function that returns an element in a list or a specified default value.

Synopsis

$LISTGET(list[,position[,default]])

Description

$LISTGET returns the requested element in the specified list as a standard character string. If the value of the position argument refers to a nonexistent member or identifies an element with an undefined value, the specified default value is returned.

The $LISTGET function is identical to the one- and two-argument forms of the $LIST function except that, under conditions that would cause $LIST to return a null string, $LISTGET returns a default value.

This function returns data of type VARCHAR.

You can use $LISTGET to retrieve a field value from a serial container field. In the following example, Home is a serial container field, the third element of which is Home_State:

SELECT Name,$LISTGET(Home,3) AS HomeState
FROM Sample.Person

Arguments

list

An encoded character string containing one or more elements. You can create a list using the SQL $LISTBUILD function or the ObjectScript $LISTBUILD function. You can convert a delimited string into a list using the SQL $LISTFROMSTRING function or the ObjectScript $LISTFROMSTRING function. You can extract a list from an existing list using the SQL $LIST function or the ObjectScript $LIST function.

position

The position argument must evaluate to an integer. If it is omitted, by default, the function examines the first element of the list. If the value of the position argument is -1, it is equivalent to specifying the last element of the list.

default

A character string. If you omit the default argument, a zero-length string is assumed for the default value.

Examples

The $LISTGET functions in the following example both return “Red”, the first element in the list:

SELECT 
    $LISTGET($LISTBUILD("Red","Blue","Green")),
    $LISTGET($LISTBUILD("Red","Blue","Green"),1)

The $LISTGET functions in the following example both return “Green”, the third and last element in the list:

SELECT 
    $LISTGET($LISTBUILD("Red","Blue","Green"),3),
    $LISTGET($LISTBUILD("Red","Blue","Green"),-1)

The $LISTGET functions in the following example both return a value upon encountering the undefined 2nd element in the list. The first returns a question mark (?), which the user defined as the default value. The second returns a null string because a default value is not specified:

SELECT 
    $LISTGET($LISTBUILD("Red",,"Green"),2,'?'),
    $LISTGET($LISTBUILD("Red",,"Green"),2)

The $LISTGET functions in the following example both specify a position greater than the last element in the three-element list. The first returns a null string because the default value is not specified. The second returns the user-specified default value, “ERR”:

SELECT 
    $LISTGET($LISTBUILD("Red","Blue","Green"),4),
    $LISTGET($LISTBUILD("Red","Blue","Green"),4,'ERR')

The $LISTGET functions in the following example both return a null string:

SELECT 
    $LISTGET($LISTBUILD("Red","Blue","Green"),0),
    $LISTGET(NULL)

Notes

Invalid Argument Values

If the expression in the list argument does not evaluate to a valid list, an SQLCODE -400 fatal error occurs because the $LISTGET return variable remains undefined. This occurs even when a default value is supplied, as in the following example:

SELECT $LISTGET('fred',1,'failsafe')

If the value of the position argument is less than -1, an SQLCODE -400 fatal error occurs because the $LISTGET return variable remains undefined. This occurs even when a default value is supplied, as in the following example:

SELECT $LISTGET($LISTBUILD("Red","Blue","Green"),-3,'failsafe')

This does not occur when position is a nonnumeric value:

SELECT $LISTGET($LISTBUILD("Red","Blue","Green"),'g','failsafe')