|string-expression||The target string from which a substring is to be extracted. An expression, which can be a field name, a string literal, a numeric, or the result of another function.|
|delimiter||A delimiter used to identify substrings.|
|from||Optional — An integer that specifies the substring, or beginning of a range of substrings, to return from the target string. Substrings are separated by a delimiter, and counted from 1. If omitted, the first substring is returned.|
|to||Optional — An integer that specifies the ending substring for a range of substrings to return from the target string. Must be used with from.|
$PIECE returns the specified substring (piece) from string-expression. The substring returned depends on the arguments used:
$PIECE(string-expression,delimiter) returns the first substring in string-expression. If delimiter occurs in string-expression, this is the substring that precedes the first occurrence of delimiter. If delimiter does not occur in string-expression, the returned substring is string-expression.
$PIECE(string-expression,delimiter,from) returns the substring which is the nth piece of string-expression, where the integer n is specified by the from argument, and pieces are separated by a delimiter. The delimiter is not returned.
$PIECE(string-expression,delimiter,from,to) returns a range of substrings including the substring specified in from through the substring specified in to. This four-argument form of $PIECE returns a string that includes any intermediate occurrences of delimiter that occur between the from and to substrings. If to is greater than the number of substrings, the returned substring includes all substrings to the end of the string-expression string.
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 data.
If you specify the null string (NULL) as the delimiter, $PIECE returns <null>, the null string.
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 the value of from is NULL, the empty string, zero, or a negative number, and no to argument is specified, $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.
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 by ",":
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:
SELECT E.Name,V.Contact FROM Sample.Employee AS E INNER JOIN Sample.Vendor AS V ON $PIECE(E.Name,',')=$PIECE(V.Contact,',')
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' 'Mumps;Measles;Chicken Pox;Diptheria' '45.23,52.76,89.05,48.27'
$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, attempted 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 substrings.
$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 null string:
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':