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:

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:

Example

Concatenate the column FIRSTNME with a blank and the column LASTNAME.

   FIRSTNME CONCAT ' ' CONCAT LASTNAME

36.
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.