$LISTFIND

$LISTFIND(list,value[,startafter])

list	An expression that evaluates to a valid list. Because lists contain encoding, list must be created using the Caché ObjectScript $LISTBUILD function, or extracted from another list using $LIST.
value	An expression containing the search element. A character string.
startafter	Optional — An 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.

$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, $LISTFIND returns a 0. The $LISTFIND function will also return a 0 if the value of the startafter argument refers to a nonexistent list member.

This function returns data of type SMALLINT.

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

   SET a=$LISTBUILD("Red","Blue","Green")
   &sql(SELECT $LISTFIND(:a,'Blue')
   INTO :b
   FROM Sample.Person)
   WRITE !,"The position is ",b

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

   SET a=$LISTBUILD("Red","Blue","Green")
   &sql(SELECT $LISTFIND(:a,'Orange')
   INTO :b
   FROM Sample.Person)
   WRITE !,"The position is ",b

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:

   SET a=$LISTBUILD("Red","Blue","Green")
   &sql(SELECT $LISTFIND(:a,'Blue',2)
   INTO :b
   FROM Sample.Person)
   WRITE !,"The position is ",b

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

   SET a=$LISTBUILD("Red","Blue","Green")
   &sql(SELECT $LISTFIND(:a,'Red',0)
   INTO :b
   FROM Sample.Person)
   WRITE !,"The position is ",b

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

   SET a=$LISTBUILD("Red","Blue","Green","Yellow","Blue")
   &sql(SELECT $LISTFIND(:a,'Blue',3)
   INTO :b
   FROM Sample.Person)
   WRITE !,"The position is ",b

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”:

   SET a=$LISTBUILD("ABC","BCD","BBB")
   &sql(SELECT $LISTFIND(:a,'B')
   INTO :b
   FROM Sample.Person)
   WRITE !,"The position is ",b

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

   SET a="Blue"
   &sql(SELECT $LISTFIND(:a,'Blue')
   INTO :b
   FROM Sample.Person)
   WRITE !,"Error code ",SQLCODE
   WRITE !,"The position is ",b

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

   SET a=$LISTBUILD("Red","Blue","Green")
   &sql(SELECT $LISTFIND(:a,'Blue',-1)
   INTO :b
   FROM Sample.Person)
   WRITE !,"Error code ",SQLCODE
   WRITE !,"The position is ",b

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

   SET a=$LISTBUILD("Red","Blue","Green")
   &sql(SELECT $LISTFIND(:a,'Blue',-3)
   INTO :b
   FROM Sample.Person)
   WRITE !,"Error code ",SQLCODE
   WRITE !,"The position is ",b