$LISTFIND (SQL)

A list function that searches a specified list for the requested value.

Synopsis

$LISTFIND(list,value[,startafter])

Description

$LISTFIND searches the specified list for the first instance of the requested value. The search begins with the element after the position indicated by the startafter argument. If you omit the startafter argument, $LISTFIND assumes a startafter value of 0 and starts the search with the first element (element 1). If the value is found, $LISTFIND returns the position of the matching element. If the value is not found or if the value of the startafter argument refers to a nonexistent list member, $LISTFIND returns a 0.

This function returns data of type SMALLINT.

Arguments

list

An expression that evaluates to a valid list. A list is an encoded character string containing one or more elements. You can create a list using the SQL or ObjectScript $LISTBUILD or $LISTFROMSTRING functions. You can extract a list from an existing list using the SQL or ObjectScript $LIST function.

value

An expression containing the search element. A character string.

startafter

An optional integer expression interpreted as a list position. The search starts with the element after this position. Zero and –1 are valid values; –1 never returns an element. Zero is the default.

Examples

The following example returns 2, the position of the first occurrence of the requested string:

SELECT $LISTFIND($LISTBUILD("Red","Blue","Green"),'Blue')

The following example returns 0, indicating the requested string was not found:

SELECT $LISTFIND($LISTBUILD("Red","Blue","Green"),'Orange')

The following three examples show the effect of using the startafter argument. The first example does not find the requested string and returns 0 because the requested string occurs at the startafter position:

SELECT $LISTFIND($LISTBUILD("Red","Blue","Green"),'Blue',2)

The second example finds the requested string at the first position by setting startafter to zero (the default value):

SELECT $LISTFIND($LISTBUILD("Red","Blue","Green"),'Red',0)

The third example finds the second occurrence of the requested string and returns 5, because the first occurs before the startafter position:

SELECT $LISTFIND($LISTBUILD("Red","Blue","Green","Yellow","Blue"),'Blue',3)

The $LISTFIND function only matches complete elements. Thus, the following example returns 0 because no element of the list is equal to the string “B”, though all of the elements contain “B”:

SELECT $LISTFIND($LISTBUILD("ABC","BCD","BBB"),'B')

Notes

Invalid Argument Values

If the expression in the list argument does not evaluate to a valid list, the $LISTFIND function generates an SQLCODE -400 fatal error.

SELECT $LISTFIND("Blue",'Blue')

If the value of the startafter argument is -1, $LISTFIND always returns zero (0).

SELECT $LISTFIND($LISTBUILD("Red","Blue","Green"),'Blue',-1)

If the value of the startafter argument is less than -1, invoking the $LISTFIND function generates an SQLCODE -400 fatal error.

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