7.26.1 Specification format and rules for concatenation operations
Concatenation operations can be specified in a value expression.
- Organization of this subsection
(1) Specification format
concatenation-operation ::= {value-expression-primary |character-string-value-expression + value-expression-primary |character-string-value-expression || value-expression-primary |binary-value-expression + value-expression-primary |binary-value-expression || value-expression-primary}
(2) Explanation of specification format
- value-expression-primary:
-
For details about value-expression-primary, see 7.20.1 Specification format and rules for value expressions.
- character-string-value-expression:
-
For details about character-string-value-expression, see 7.20.1 Specification format and rules for value expressions.
- binary-value-expression:
-
For details about binary-value-expression, see 7.20.1 Specification format and rules for value expressions.
(3) Types of concatenation operations
The types of concatenation operations are shown in the following table.
No. |
Concatenation operation |
Function |
---|---|---|
1 |
+ |
Concatenate the first operand and the second operand. |
2 |
|| |
For example, if the operation is 'ABC'+'DEF', the first operand is 'ABC', and the second operand is 'DEF'.
(4) Rules
-
The first and second operands must both be either character string data or binary data.
-
The following table shows the combinations of data types that can be specified in the first and second operands.
Table 7‒35: Combinations of data types that can be specified in the first and second operands in a concatenation operation Data type of the first operand
Data type of the second operand
CHAR
VARCHAR
BINARY
VARBINARY
CHAR
Y
Y
N
N
VARCHAR
Y
Y
N
N
BINARY
N
N
Y
Y
VARBINARY
N
N
Y
Y
- Legend:
-
Y: Can be specified.
N: Cannot be specified.
-
Concatenation operations with up to 500 operators (+, ||) can be performed. If an operand is a value expression with a column from a viewed table, derived table, or query name, the total number of value expressions after expanding the value expression it is based on cannot exceed 10,000.
-
You cannot specify a dynamic parameter by itself for the first operand or second operand.
-
The NOT NULL constraint does not apply to the value of the result of the concatenation operation (the null value is allowed).
-
If either the first operand or second operand has the null value, the result of the concatenation operation will be a null value.
-
You cannot concatenate character string data or binary data if the result of the concatenation operation would exceed the maximum 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.
"C1"+"C2" 'ABC XYZ'
"C2"+"C1" 'XYZABC'
Legend:
Δ: Single-byte space
(5) Example
- Example 1: Concatenate character string data
-
This example finds the rows in table T1 for which the result of concatenating the character string data in columns C2 and C3 is 'efg03v03'.
SELECT * FROM "T1" WHERE "C2"||"C3"='efg03v03'
- Example 2: Concatenate binary data
-
This example finds the rows in table T1 for which the result of concatenating the binary data in columns C2 and C3 is X'ABC1230000DEF456'.
SELECT * FROM "T1" WHERE "C2"||"C3"=X'ABC1230000DEF456'