Skip to main content

$LISTBUILD (SQL)

A list function that builds a list from strings.

Synopsis

$LISTBUILD(element [,...])

Description

$LISTBUILD takes one or more expressions and returns a list with one element for each expression.

The following functions can be used to create a list:

  • $LISTBUILD, which creates a list from multiple strings, one string per element.

  • $LISTFROMSTRING, which creates a list from a single string containing multiple delimited elements.

  • $LIST, which extracts a sublist from an existing list.

$LISTBUILD is used with the other InterSystems SQL list functions: $LIST, $LISTDATA, $LISTFIND, $LISTFROMSTRING, $LISTGET, $LISTLENGTH, and $LISTTOSTRING.

Note:

$LISTBUILD and the other $LIST functions use an optimized binary representation to store data elements. For this reason, equivalency tests may not work as expected with some $LIST data. Data that might, in other contexts, be considered equivalent, may have a different internal representation. For example, $LISTBUILD(1) is not equal to $LISTBUILD('1').

For the same reason, a list string value returned by $LISTBUILD should not be used in character search and parse functions that use a delimiter character, such as $PIECE and the two-argument form of $LENGTH. Elements in a list created by $LISTBUILD are not marked by a character delimiter, and thus can contain any character.

Arguments

element

Any expression, or comma-separated list of expressions.

Examples

The following example takes three strings and produces a three-element list:

SELECT $LISTBUILD("Red","White","Blue")

Notes

Omitting Arguments

Omitting an element expression yields an element whose value is NULL. For example, the following example contains two $LISTBUILD statements that both produce a three-element list whose second element has an undefined (NULL) value:

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

Additionally, if a $LISTBUILD expression is undefined, the corresponding list element has an undefined value. The following example produces a two-element list whose first element is "Red" and whose second element has an undefined value:

SELECT $LISTBUILD('Red',:z)

The following example produces a two-element list. The trailing comma indicates the second element has an undefined value:

SELECT $LISTBUILD('Red',)

Providing No and NULL Arguments

Invoking the $LISTBUILD function with no arguments or with NULL returns a list with one element whose data value is NULL. The following are valid $LISTBUILD statements that create “empty” lists:

SELECT $LISTBUILD(), $LISTBUILD(NULL)

The following are valid $LISTBUILD statements that create a list element that contains an empty string:

SELECT $LISTBUILD(''), $LISTBUILD(CHAR(0))

Nesting Lists

An element of a list may itself be a list. For example, the following statement produces a three-element list whose third element is the two-element list, "Walnut,Pecan":

SELECT $LISTBUILD('Apple','Pear',$LISTBUILD('Walnut','Pecan'))

Concatenating Lists

The result of concatenating two lists with the SQL Concatenate operator (||) is another list. For example, the following SELECT items produce the same list, "A,B,C":

SELECT $LISTBUILD('A','B','C') AS List,
  $LISTBUILD('A','B')||$LISTBUILD('C') AS CatList

In the following example, the first two select items result in the same two-element list; the third select item results in NULL (because concatenating NULL to anything results in NULL); the fourth and fifth select items result in the same three-element list:

SELECT
  $LISTBUILD('A','B') AS List,
  $LISTBUILD('A','B')||'' AS CatEStr,
  $LISTBUILD('A','B')||NULL AS CatNull,
  $LISTBUILD('A','B')||$LISTBUILD('') AS CatEList,
  $LISTBUILD('A','B')||$LISTBUILD(NULL) AS CatNList

Unicode

If one or more characters in a list element is a wide (Unicode) character, all characters in that element are represented as wide characters. To ensure compatibility across systems, $LISTBUILD always stores these bytes in the same order, regardless of the hardware platform. Wide characters are represented as byte strings. For further details, refer to the ObjectScript $LISTBUILD function.

See Also

Purpose of this instance: Draft documentation
DocReleaseID: HealthConnect2025.1
Content loaded from:
/staging/learning/iris-doc/2025.1.x/doc/cache/en-us/src/
/staging/learning/iris-doc/2025.1.x/doc/ensemble/en-us/src/
/staging/learning/iris-doc/2025.1.x/doc/healthconnect/en-us/src/
/staging/learning/iris-doc/2025.1.x/doc/healthcommon/en-us/src/
/staging/learning/iris-doc/2025.1.x/doc/security/en-us/src/
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
FeedbackOpens in a new tab