Caché 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 Caché ObjectScript $LISTBUILD or $LISTFROMSTRING functions. You can extract a list from an existing list using the SQL or Caché 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 a Caché 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. Caché 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 Caché ObjectScript $LISTBUILD
function. You can convert a delimited string into a list using the SQL $LISTFROMSTRING
function or the Caché ObjectScript $LISTFROMSTRING
function. You can extract a list from an existing list using the SQL $LIST
function or the Caché 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