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
(2) Explanation of specification format
- 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'
(3) Rules
-
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)
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(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
(4) Example
- 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'