Expressions with the concatenation operator

When two strings operands are concatenated, the result of the expression is a string.

Introductory concepts

The operands of concatenation must be compatible strings. A binary string cannot be concatenated with a character string, including character strings that are defined as FOR BIT DATA (for more information on the compatibility of data types, see the compatibility matrix in Table 1). A distinct type that is based on a string type can be concatenated only if an appropriate user-defined function is created.

Both CONCAT and the vertical bars (||) represent the concatenation operator. Vertical bars (or the characters that must be used in place of vertical bars in some countries1) can cause parsing errors in statements passed from one DBMS to another. The problem occurs if the statement undergoes character conversion with certain combinations of source and target CCSIDs1. Thus, CONCAT is the preferable concatenation operator.

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.

The following table shows how the string operands determine the data type and the length attribute of the result (the order in which the operands are concatenated has no effect on the result).
Table 1. Data type and length of concatenated operands
If one
operand column is
And the other
operand is
 The data type of the result column is1
CHAR(x) CHAR(y) with a combined length attribute that is less than 256 CHAR(x+y)2
CHAR(y) with a combined length attribute that is greater than 255 VARCHAR(MIN(x'+y',32704))3
VARCHAR(y)
VARCHAR(x) VARCHAR(y) VARCHAR(MIN(x'+y',32704))3
CLOB(x) CHAR(y) CLOB(MIN(x'+y',2G))
VARCHAR(y)
CLOB(y)
GRAPHIC(y) DBCLOB(MIN(x+y,1G))
VARGRAPHIC(y)
DBCLOB(y)
GRAPHIC(x) CHAR(y) VARGRAPHIC(MIN(x+y,16352)) 4
VARCHAR(y)
VARGRAPHIC(y)
VARGRAPHIC(x) CHAR(y) VARGRAPHIC(MIN(x+y,16352)) 4
VARCHAR(y)
GRAPHIC(y)
GRAPHIC(y)
DBCLOB(x) CHAR(y) DBCLOB(MIN(x+y,1G))
VARCHAR(y)
CLOB(y)
GRAPHIC(y)
VARGRAPHIC(y)
DBCLOB(y)
BINARY(x) BINARY(y) with a combined length attribute that is less than 256 BINARY(x+y)
BINARY(y) with a combined length attribute that is greater than 255 VARBINARY( MIN(x+y,32704))
VARBINARY(x) VARBINARY(y) VARBINARY( MIN(x+y,32704))
BINARY(y)
BLOB(x) BLOB(y) BLOB(MIN(x+y, 2G))
Notes:
    • 2G represents 2,147,483,647 bytes
    • 1G represents 1,073,741,823 double-byte characters
  2. Neither CHAR(x) nor CHAR(y) can contain mixed data. If either operand contains mixed data, the result is VARCHAR(MIN(x'+y',32704)).
  3. If conversion of the first operand is required, x' = 3x; otherwise, it remains x. If conversion of the second operand is required, y'= 3y; otherwise, it remains y.
  4. Both operands are converted to UTF-16, if necessary (that is, the operand is not already UTF-16), and the results are concatenated.

As the previous table shows, the length of the result is the sum of the lengths of the operands. However, the length of the result is two bytes less if redundant shift code characters are eliminated from the result. Redundant shift code characters exist when both character strings are EBCDIC mixed data, and the first string ends with a "shift-in" character (X'0F') and the second operand begins with a "shift-out" character (X'0E'). These two shift code characters are removed from the result.

The CCSID of the result is determined by the rules set forth in Character conversion in set operations and concatenations. Some consequences of those rules are the following:

  • If either operand is BIT data, the result is BIT data.
  • The conversion that occurs when SBCS data is compared with mixed data depends on the encoding scheme. If the encoding scheme is Unicode, the SBCS operand is converted to MIXED. Otherwise, the conversion depends on the field MIXED DATA on installation panel DSNTIPF for the DB2 that does the comparison:
    • Mixed data if the MIXED DATA option at the server is YES. The result is not necessarily well-formed mixed data.
    • SBCS data if the MIXED DATA option at the server is NO. If the mixed data cannot be converted to pure SBCS data, an error occurs.

If an operand is a string from a column with a field procedure, the operation applies to the decoded form of the value. The result does not inherit the field procedure.

One operand of concatenation can be a parameter marker. When one operand is a parameter marker, its data type and length attributes are considered to be the same as those for the operand that is not a parameter marker except for a string data type. Refer to Table 1 for the formula used to calculate data type length for untyped parameter markers in the CONCAT operator when another operand is a string data type. The order of concatenation operations must be considered to determine these attributes in the case of nested concatenation.

No operand of concatenation can be a distinct type even if the distinct type is based on a character data type. To concatenate a distinct type, create a user-defined function that is sourced on the CONCAT operator. For example, if distinct types TITLE and TITLE_DESCRIPTION were both sourced on data type VARCHAR(25), the following user-defined function, named ATTACH, could be used to concatenate the two distinct types: 
   CREATE FUNCTION ATTACH (TITLE, TITLE_DESCRIPTION)
      RETURNS VARCHAR(50) SOURCE CONCAT (VARCHAR(), VARCHAR())
Alternatively, the concatenation operator could be overloaded by using a user-defined function to add the distinct types: 
   CREATE FUNCTION "||" (TITLE, TITLE_DESCRIPTION)
      RETURNS VARCHAR(50) SOURCE CONCAT (VARCHAR(), VARCHAR())
1 In various EBCDIC code pages, DB2 supports code point combinations X'4F4F', X'BBBB', and X'5A5A' to mean concatenation. X'BBBB' and X'5A5A' are interpreted to mean concatenation only on single byte character set DB2 subsystems.