Caché SQL Reference
$LISTTOSTRING
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

A list function that creates a string from a list.
Synopsis
$LISTTOSTRING(list,delimiter)
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 Caché ObjectScript $LISTBUILD or $LISTFROMSTRING functions. You can extract a list from an existing list using the SQL or Caché ObjectScript $LIST function.
delimiter 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.
Description
$LISTTOSTRING 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 function.
Note:
The delimiter 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
FROM Sample.Person
 
Arguments
list
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 function.
If the expression in the list argument does not evaluate to a valid list, an SQLCODE -400 error occurs.
delimiter
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.
Example
The following example converts the values of a list field to a string with the elements delimited by the colon (:) character:
SELECT
Name,
FavoriteColors AS ColorList,
$LISTTOSTRING(FavoriteColors,':') AS ColorStrings
FROM Sample.Person
WHERE FavoriteColors IS NOT NULL
 
See Also