8.5.1 CONCAT

Concatenates two character string data items.

For the scalar function that concatenates binary data, see 8.10.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 character string 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 CHAR or VARCHAR 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 the two character strings ABC and XYZ.

CONCAT('ABC','XYZ') → 'ABCXYZ'

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

Table 8‒11: 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
`CHAR(m)`	`CHAR(n)`	`CHAR(m+n)`
`CHAR(m)`	`VARCHAR(n)` Actual data length: `L2`	`VARCHAR(m+n)` Actual data length: `m`+`L2`
`VARCHAR(m)` Actual data length: `L1`	`CHAR(n)`	`VARCHAR(m+n)` Actual data length: `L1`+`n`
`VARCHAR(m)` Actual data length: `L1`	`VARCHAR(n)` Actual data length: `L2`	`VARCHAR(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 character string length of 32,000 bytes.
Spaces at the end of the character string data are also subject to concatenation.

Example

If column C1 is type CHAR(5) with a value of 'ABC', and column C2 is type VARCHAR(10) with a value of 'XYZ', the following concatenations are performed.

CONCAT("C1","C2") → 'ABCXYZ'

CONCAT("C2","C1") → 'XYZABC'

Legend:

Δ: Single-byte space

Example:

Find the rows in table T1 for which the execution result of concatenating character string data in columns C2 and C3 is efg03v03.

SELECT * FROM "T1"
    WHERE CONCAT("C2","C3")='efg03v03'

[Figure]