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, date, time, or timestamp data types.1 The operands must not be distinct types. If a numeric, date, time, or timestamp 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 unless the character string is 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:
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) or CHAR(y) FOR BIT DATA or VARCHAR(y) FOR BIT DATA | BLOB(z) where z = MIN(x + y, maximum length of a BLOB) |
VARBINARY(x) | BINARY(y) or VARBINARY(y) or CHAR(y) FOR BIT DATA or VARCHAR(y) FOR BIT DATA | VARBINARY(z) where z = MIN(x + y, maximum length of a VARBINARY) |
BINARY(x) | VARCHAR(y) FOR BIT DATA | VARBINARY(z) where z = MIN(x + y, maximum length of a VARBINARY) |
BINARY(x) | BINARY(y) or CHAR(y) FOR BIT DATA | 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. |
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 attribute of the result is the maximum length of the resulting data type.2
- 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