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 specification rules apply:

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

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

Example:

Concatenate two character string data items (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`)
	VARCHAR(`n`) Data length: `L2`	If `m` + `n` ≤ 32,000 VARCHAR(`m`+`n`) Actual data length: `m` + `L2` If `m` + `n` > 32,000 STRING Actual data length: `m` + `L2`
	STRING Data length: `L2`	STRING Actual data length: `m` + `L2`
VARCHAR(`m`) Actual data length: `L1`	CHAR(`n`)	If `m` + `n` ≤ 32,000 VARCHAR(`m`+`n`) Actual data length: `L1` + `n` If `m` + `n` > 32,000 STRING Actual data length: `L1` + `n`
	VARCHAR(`n`) Data length: `L2`	If `m` + `n` ≤ 32,000 VARCHAR(`m`+`n`) Actual data length: `L1` + `L2` If `m` + `n` > 32,000 STRING Actual data length: `L1` + `L2`
	STRING Data length: `L2`	STRING Actual data length: `L1` + `L2`
STRING Actual data length: `L1`	CHAR(`n`)	STRING Actual data length: `L1` + `n`
	VARCHAR(`n`) Data length: `L2`	STRING Actual data length: `L1` + `L2`
	STRING Data length: `L2`	STRING Actual data length: `L1` + `L2`

Legend:

m: Maximum length of target-data-1

n: Maximum length of target-data-2

L1: Actual length of target-data-1

L2: Actual 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.
If the actual length of the data concatenated from target-data-1 and target-data-2 exceeds the maximum length of the data (not the actual data length) of the execution result shown in Table 8‒11: Data type and data length of the execution result of the scalar function CONCAT, then target-data-1 and target-data-2 cannot be concatenated.
If the concatenated data of target-data-1 and target-data-2 results in CHAR type data with a maximum length exceeding 32,000 bytes, then target-data-1 and target-data-2 cannot be concatenated.
If a half-width space is stored at the end of the CHAR type data, it is also concatenated.

Example:

If column C1 is CHAR(5) and the value is ' ABCΔΔ' and column C2 is VARCHAR(10) and the value is 'XYZ', they are concatenated as follows

CONCAT("C1","C2") → 'ABCΔΔXYZ'

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

Legend: Δ: Half-width 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]