InterSystems SQL Reference
A list function that creates a string from a 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.
||Optional A delimiter inserted to separate substrings. A delimiter can be one or more characters, specified as a quoted string. To concatenate the substrings with no delimiter, specify the empty string (''). If you specify no delimiter, the default is the comma (,) character.
takes an InterSystems IRIS list and converts it to a string. In the resulting string, the elements of the list are separated by the delimiter
A list represents data in an encoded format which does not use delimiter characters. Thus a list can contain all possible characters, and is ideally suited for bitstring data. $LISTTOSTRING
converts this list to a string with delimited elements. It sets aside a specified character (or character string) to serve as a delimiter. These delimited elements can be handled using the $PIECE
specified here must not occur in the source data. InterSystems IRIS makes no distinction between a character serving as a delimiter and the same character as a data character.
You can use $LISTTOSTRING
to retrieve field values from a serial container field as a delimited string. In the following example, Home is a serial container field. It contains the list elements Home_Street, Home_City, Home_State, and Home_Zip:
SELECT Name,$LISTTOSTRING(Home,'^') AS HomeAddress
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
If the expression in the list
argument does not evaluate to a valid list, an SQLCODE -400 error occurs.
A character (or string of characters) used to delimit substrings within the output string. It can be a numeric or string literal (enclosed in single quotation marks), a host variable, or an expression that evaluates to a string.
Commonly, a delimiter is a designated character which is never used within string data, but is set aside solely for use as a delimiter separating substrings. A delimiter can also be a multi-character string, the individual characters of which can be used within string data.
If you specify no delimiter, the default delimiter is the comma (,) character. You can specify a null string ('') as a delimiter; in this case, substrings are concatenated with no delimiter. To specify the single quote character as the delimiter, duplicate the quote character thus: ''''
four single quote characters.
The following example converts the values of a list field to a string with the elements delimited by the colon (:) character:
FavoriteColors AS ColorList,
$LISTTOSTRING(FavoriteColors,':') AS ColorStrings
WHERE FavoriteColors IS NOT NULL
Content Date/Time: 2019-04-10 14:45:57