$LISTLENGTH (SQL)

A list function that returns the number of elements in a specified list.

Synopsis

$LISTLENGTH(list)

Description

$LISTLENGTH returns the number of elements in list.

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.

Examples

The following example returns 3, because there are 3 elements in the list:

SELECT $LISTLENGTH($LISTBUILD("Red","Blue","Green"))

The following SQL example also returns 3, because there are 3 elements in the list:

SELECT $LISTLENGTH($LISTBUILD('Red','Blue','Green'))

The following example also returns 3. There are 3 elements in the list, though the second element contains no data:

SELECT $LISTLENGTH($LISTBUILD("Red",,"Green"))

In the following SQL example, each $LISTLENGTH returns 3, because there are 3 elements in the list, though the second element contains no data:

SELECT $LISTLENGTH($LISTBUILD('Red','','Green')),
       $LISTLENGTH($LISTBUILD('Red',NULL,'Green')),
       $LISTLENGTH($LISTBUILD('Red',,'Green'))

Notes

Invalid Lists

If list is not a valid list, an SQLCODE -400 fatal error is generated:

SELECT $LISTLENGTH("fred")

If the ObjectScript $LISTBUILD function is used to build a list that contains only the null string, this is a valid list, containing one element:

SELECT $LISTLENGTH($LISTBUILD(""))

Null Lists

The SQL $LISTLENGTH function and the ObjectScript $LISTLENGTH function differ in how they handle a null list (a list containing no elements).

The following three examples show how the $LISTLENGTH SQL function handles a null list. In the first two examples, list is the null string, and a null string is returned:

SELECT $LISTLENGTH("")

SELECT $LISTLENGTH(NULL)

In the third example, list is the value $CHAR(0), which is an invalid list; an SQLCODE -400 fatal error is generated:

SELECT $LISTLENGTH('')

Note that this differs from how the ObjectScript $LISTLENGTH function handles a null list. In ObjectScript, the null string ("") is used to represent a null list, a list containing no elements. Because it contains no list elements, it has a $LISTLENGTH count of 0, as shown in the following example:

   WRITE $LISTLENGTH("")

$LISTLENGTH and Nested Lists

The following example returns 3, because $LISTLENGTH does not recognize the individual elements in nested lists:

SELECT $LISTLENGTH($LISTBUILD("Apple","Pear",$LISTBUILD("Walnut","Pecan")))

Excluded items: AADAN, ABIT, ACLOUD, AFL_adonet, AFL_ecp, AFL_icm, AFL_jdbc, AFL_netxep, AFL_sharding, AFL_xep, APMML, APOWER, ARGATEWAY, AWEBSOCKETS, AWSGI, BJAVA, BJAVNAT, BJAVPER, BJAVXEP, BJSNAT, BNET, BNETNAT, BNETODBC, BNETXEP, BPYNAT, BSPK, BTPI, BXCI, GAUTOML, GCONV, GCSP, GICM, GIKNOW, GIML, GISQ, GISR, GSCF, GSQLSRCH, GTSQ, GUIMA, PAGE_activex, PAGE_administration, PAGE_apimgr, PAGE_appdev_ides, PAGE_bi, PAGE_cachedirect, PAGE_data_migration, PAGE_data_science, PAGE_deployment, PAGE_deployment_configuration, PAGE_deployment_containers, PAGE_deployment_icm, PAGE_deployment_iko, PAGE_deployment_install, PAGE_dotnet, PAGE_dotnet_native, PAGE_dsvr, PAGE_embedded_language_development, PAGE_epython, PAGE_external_language_development, PAGE_interop, PAGE_interop_lifecycle, PAGE_interop_vdoc, PAGE_interoperability, PAGE_java, PAGE_java_jdbc, PAGE_java_native, PAGE_machine_learning, PAGE_monitoring, PAGE_multimodel, PAGE_multimodel_multidimensional, PAGE_multimodel_object, PAGE_multivalue, PAGE_native, PAGE_nodejs_native, PAGE_objectscript, PAGE_objectscript_ides, PAGE_odbc, PAGE_platform_arch_design, PAGE_pygate, PAGE_python, PAGE_python_native, PAGE_reports, PAGE_scalability, PAGE_security_authentication, PAGE_security_authorization, PAGE_studio, PAGE_zen, PAGE_zenmojo, PAGE_zenreports