A string function that returns a substring identified
by a delimiter.
The string from which the substring is to be returned. It can
be a string literal, a variable name, or any valid expression that
evaluates to a string.
A string usually contains instances of a character (or character
string) which are used as delimiters. This character or string cannot
also be used as a data value within string-expression.
If you specify the null string (NULL) as the target string, $PIECE returns <null>, the null string.
The search string to be used to delimit substrings within string-expression. It can be a numeric or string literal
(enclosed in quotation marks), the name of a 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 search
string, the individual characters of which can be used within string
If you specify the null string (NULL) as the delimiter, $PIECE returns <null>, the null string.
An optional argument specifying the number of a substring within string-expression, counting from 1. It must be a positive integer, the name of an
integer variable, or an expression that evaluates to a positive integer.
Substrings are separated by delimiters.
If the from argument is omitted
or set to 1, $PIECE returns the first substring
of string-expression. If string-expression does not contain the specified delimiter, a from value of 1 returns string-expression.
If the from argument identifies
by count the last substring in string-expression, this substring is returned, regardless of whether it is followed
by a delimiter.
If no to argument is specified
and the value of from is NULL, the empty string,
zero, or a negative number, $PIECE returns a
null string. However, if a to argument is specified, $PIECE treats these from values the
same as from=1.
If the value of from is greater
than the number of substrings in string-expression, $PIECE returns a null string.
If the from argument is used with the to argument, it identifies the start of a range of substrings
to be returned as a string, and should be less than the value of to.
An optional argument specifying the number of the substring within string-expression that ends the range initiated by the from argument.
The returned string includes both the from and to substrings, as well as any intermediate substrings and
the delimiters separating them. The to argument
must be a positive integer, the name of an integer variable, or an
expression that evaluates to a positive integer. The to argument must be used with from and should be
greater than the value of from.
If from is less than to, $PIECE returns a string consisting of all
of the delimited substrings within this range, including the from and to substrings. This returned
string contains the substrings and the delimiters within this range.
If to is greater than the number
of delimited substrings, the returned string contains all the string
data (substrings and delimiters) beginning with the from substring and continuing to the end of the string-expression string.
If from is equal to to, the from substring is returned.
If from is greater than to, $PIECE returns a null string.
If to is the null string (NULL), $PIECE returns a null string.
The following example returns 'Red', the first substring as
identified by the "," delimiter:
The following example returns 'Blue', the third substring as
identified by the "," delimiters:
The following example returns 'Blue,Yellow,Orange', the third
through fifth elements in colorlist, as delimited
The following $PIECE functions both return
'123', showing that the two-argument form is equivalent to the three-argument
form when from is 1:
SELECT $PIECE('123#456#789','#') AS TwoArg
SELECT $PIECE('123#456#789','#',1) AS ThreeArg
The following example uses the multi-character delimiter string
'#-#' to return the third substring '789'. Here, the component characters
of the delimiter string, '#' and '-', can be used as data values;
only the specified sequence of characters (#-#) is set aside:
The following example returns 'MAR;APR;MAY'. These comprise
the third through the fifth substrings, as identified by the ';' delimiter:
The following example uses $PIECE to extract
the surname from employee names and vendor contact names, and then
perform a JOIN which return instances where an employee has the same
surname as a vendor contact:
FROM Sample.Employee AS E INNER JOIN Sample.Vendor AS V
Using $PIECE to Unpack Data Values
$PIECE is typically used to "unpack" data
values that contain multiple fields delimited by a separator character.
Typical delimiter characters include the slash (/), the comma (,),
the space ( ), and the semicolon (;). The following sample values
are good candidates for use with $PIECE:
'John Jones/29 River St./Boston MA, 02095'
$PIECE and $LENGTH
The two-argument form of $LENGTH returns
the number of substrings in a string, based on a delimiter. Use $LENGTH to determine the number of substrings in a string,
and then use $PIECE to extract individual substrings.
$PIECE and $LIST
The data storage techniques used by $PIECE and the $LIST functions are incompatible and
should not be combined. For example, attempting to use $PIECE on a list created using $LISTBUILD yields unpredictable
results and should be avoided. This is true for both SQL functions
and the corresponding ObjectScript functions.
The $LIST functions specify substrings
without using a designated delimiter. If setting aside a delimiter
character or character sequence is not appropriate to the type of
data (for example, bitstring data), you should use the $LISTBUILD and $LIST SQL functions to store and retrieve
$PIECE does not distinguish between a delimited
substring with a null string value (NULL), and a nonexistent substring.
Both return <null>, the null string value. For
example, the following examples both return the null string for a from value of 7:
In the first case, there is no seventh substring; a null string
is returned. In the second case there is a seventh substring, as indicted
by the delimiter at the end of the string-expression string; the value of this seventh substring is the null string.
The following example shows null values within a string-expression. It extracts substrings 3. This substring exists, but contains a
The following examples also returns a null string, because the
specified substrings do not exist:
In the following example, the $PIECE function
returns the entire string-expression string, because
there are no occurrences of delimiter in the string-expression string:
Nested $PIECE Operations
To perform complex extractions, you can nest $PIECE references within each other. The inner $PIECE returns a substring that is operated on by the outer $PIECE. Each $PIECE uses its own delimiter. For example,
the following returns the state abbreviation 'MA':
SELECT $PIECE($PIECE('John Jones/29 River St./Boston MA 02095','/',3),' ',2)
The following is another example of nested $PIECE operations, using a hierarchy of delimiters. First, the inner $PIECE uses the caret (^) delimiter to find the second
piece, 'A,B,C', of the string. Then the outer $PIECE uses the comma (,) delimiter to return the first and second pieces
('A,B') of the substring 'A,B,C':