8.10.1 CONCAT

Concatenates two binary data items.

For the scalar function that concatenates character data, see 8.5.1 CONCAT.

Organization of this subsection

(1) Specification format

scalar-function-CONCAT ::= CONCAT(target-data-1,target-data-2)
 
  target-data-1 ::= value-expression
  target-data-2 ::= value-expression

target-data-1 and target-data-2:

Specifies the binary data to be concatenated.

The following rules apply:

Specify target-data-1 and target-data-2 in the form of value expressions. For details about value expressions, see 7.20 Value expression.
Specify BINARY or VARBINARY type data for target-data-1 and target-data-2.
You cannot specify a dynamic parameter by itself for target-data-1 and target-data-2.

The following example illustrates the result of executing the scalar function CONCAT.

Example:

Concatenate two binary data items (X'ABC1230000' and X'DEF456').

CONCAT(X'ABC1230000',X'DEF456') → X'ABC1230000DEF456'

The data type and data length of the execution result are shown in the following table.

Table 8‒32: Data type and data length of the execution result of the scalar function CONCAT
Data type and data length of target-data-1	Data type and data length of target-data-2	Data type and data length of the execution result
`BINARY(m)`	`BINARY(n)`	`BINARY(m` + `n)`
`BINARY(m)`	`VARBINARY(n)` Actual data length: `L2`	`VARBINARY(m` + `n)` Actual data length: `m` + `L2`
`VARBINARY(m)` Actual data length: `L1`	`BINARY(n)`	`VARBINARY(m` + `n)` Actual data length: `L1` + `n`
`VARBINARY(m)` Actual data length: `L1`	`VARBINARY(n)` Actual data length: `L2`	`VARBINARY(m` + `n)` Actual data length: `L1` + `L2`

Legend:

m: Maximum length of target-data-1

n: Maximum length of target-data-2

L1: Actual data length of target-data-1

L2: Actual data length of target-data-2

The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
If either target-data-1 or target-data-2 has a null value, the execution result will be a null value.
You cannot concatenate target-data-1 and target-data-2 if the result of the concatenation operation would exceed the maximum binary data length of 32,000 bytes.