With the concatenation operator
If the concatenation operator (CONCAT or ||) is used, the result of the
expression is a string.
The operands of concatenation must be compatible strings or numeric data
types. The operands must not be distinct types. If a numeric operand is specified,
it is CAST to the equivalent character string prior to concatenation. Note
that a binary string cannot be concatenated with a character string, including
character strings defined as FOR BIT DATA.
The data type of the result is determined by the data types of the operands.
The data type of the result is summarized in the following table:
Table 23. Result Data Types With Concatenation
If one operand column is ... |
And the other operand is ... |
The data type of the result column is
... |
DBCLOB(x) |
CHAR(y)* or VARCHAR(y)*
or CLOB(y)* or GRAPHIC(y) or VARGRAPHIC(y) or DBCLOB(y) |
DBCLOB(z) where z = MIN(x + y, maximum length
of a DBCLOB) |
VARGRAPHIC(x) |
CHAR(y)* or VARCHAR(y)*
or GRAPHIC(y) or VARGRAPHIC(y) |
VARGRAPHIC(z) where z = MIN(x + y, maximum
length of a VARGRAPHIC) |
GRAPHIC(x) |
CHAR(y)* mixed data |
VARGRAPHIC(z) where z = MIN(x + y, maximum
length of a VARGRAPHIC) |
GRAPHIC(x) |
CHAR(y)* SBCS data or GRAPHIC(y) |
GRAPHIC(z) where z = MIN(x + y, maximum length
of a GRAPHIC) |
CLOB(x)* |
GRAPHIC(y) or VARGRAPHIC(y) |
DBCLOB(z) where z = MIN(x + y, maximum length
of a DBCLOB) |
VARCHAR(x)* |
GRAPHIC(y) |
VARGRAPHIC(z) where z = MIN(x + y, maximum
length of a VARGRAPHIC) |
CLOB(x) |
CHAR(y) or VARCHAR(y) or CLOB(y) |
CLOB(z) where z = MIN(x + y, maximum length
of a CLOB) |
VARCHAR(x) |
CHAR(y) or VARCHAR(y) |
VARCHAR(z) where z = MIN(x + y, maximum length
of a VARCHAR) |
CHAR(x) mixed data |
CHAR(y) |
VARCHAR(z) where z = MIN(x + y, maximum length
of a VARCHAR) |
CHAR(x) SBCS data |
CHAR(y) |
CHAR(z) where z = MIN(x + y, maximum length
of a CHAR) |
BLOB(x) |
BINARY(y) or VARBINARY(y) or BLOB(y) |
BLOB(z) where z = MIN(x + y, maximum length
of a BLOB) |
VARBINARY(x) |
BINARY(y) or VARBINARY(y) |
VARBINARY(z) where z = MIN(x + y, maximum
length of a VARBINARY) |
BINARY(x) |
BINARY(y) |
BINARY(z) where z = MIN(x + y, maximum length
of a BINARY) |
Note:
* Character
strings are only allowed when the other operand is a graphic string if the
graphic string is Unicode. |
Table 24. Result Encoding Schemes With Concatenation
If one operand column is ... |
And the other operand is ... |
The data type of the result column is
... |
Unicode data |
Unicode data or DBCS or mixed or SBCS data |
Unicode data |
DBCS data |
DBCS data |
DBCS data |
bit data |
mixed or SBCS or bit data |
bit data |
mixed data |
mixed or SBCS data |
mixed data |
SBCS data |
SBCS data |
SBCS data |
If the sum of the lengths of the operands exceeds the maximum length attribute
of the resulting data type:
- The length atttribute of the result is the maximum length of the resulting
data type.36
- If only blanks are truncated no warning or error occurs.
- If non-blanks are truncated, an error occurs.
If either operand can be null, the result can be null, and if either is
null, the result is the null value. Otherwise, the result consists of the
first operand string followed by the second.
With mixed data this result will not have redundant shift codes "at the
seam". Thus, if the first operand is a string ending with a "shift-in" character (X'0F'), while the second operand is a character string
beginning with a "shift-out" character (X'0E'), these two bytes
are eliminated from the result.
The actual length of the result is the sum of the lengths of the operands
unless redundant shifts are eliminated; in which case, the actual length is
two less than the sum of the lengths of the operands.
The CCSID of the result is determined by the CCSID of the operands as explained
under Conversion rules for operations that combine strings. Note that as a result of these rules:
- If any operand is bit data, the result is bit data.
- If one operand is mixed data and the other is SBCS data, the result is
mixed data. However, this does not necessarily mean that the result is well-formed
mixed data.
Example
Concatenate the column FIRSTNME with a blank and the column LASTNAME.
FIRSTNME CONCAT ' ' CONCAT LASTNAME
If the expression is in the select-list, the length attribute
may be further reduced in order to fit within the maximum record size. For
more information, see
Maximum row sizes.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.