# 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`

^{1}Using the vertical bar (|) character might inhibit code portability between relational database products. Use the CONCAT operator in place of the || operator. Alternatively, if conformance to SQL 2003 Core standard is of primary importance, use the || operator).

^{2}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.