7.27.1 Specification format and rules for concatenation operations

(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}

To Page Top

(2) Explanation of specification format

value-expression-primary:

For details about value-expression-primary, see 7.21.1 Specification format and rules for value expressions.

character-string-value-expression:

For details about character-string-value-expression, see 7.21.1 Specification format and rules for value expressions.

binary-value-expression:

For details about binary-value-expression, see 7.21.1 Specification format and rules for value expressions.

To Page Top

(3) Types of concatenation operations

The types of concatenation operations are shown in the following table.

Table 7‒46: Types of concatenation operations

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'.

To Page Top

(4) Rules

1. Specify character data or binary data for the first and second operation operands.

2. The following table shows the data type combinations that can be specified for the first and second operation operands.

   Table 7‒47: Combinations of data types that can be specified for the first and second operation operands of the concatenation operation

   Data type of the first operand	Data type of the second operand
   	CHAR	VARCHAR	STRING	BINARY	VARBINARY
   CHAR	Y	Y	Y	N	N
   VARCHAR	Y	Y	Y	N	N
   STRING	Y	Y	Y	N	N
   BINARY	N	N	N	Y	Y
   VARBINARY	N	N	N	Y	Y

   Legend:

   Y: Can be specified.

   N: Cannot be specified.

3. Concatenation operations can use a maximum of 500 arithmetic operators (+, ||). 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.

4. You cannot specify a dynamic parameter by itself for the first and second operation operands.

5. The NOT NULL constraint does not apply to the value of the concatenation operation result (the null value is allowed).

6. If either the first or second operation operand is a null value, the result of the concatenation operation is a null value.

7. Binary data whose maximum length exceeds 32,000 bytes as a result of the concatenation operation cannot be concatenated.

8. If the data type of the result of the concatenation operation is of type CHAR, character strings whose maximum length exceeds 32,000 bytes cannot be concatenated.

9. If the actual length of the data resulting from the concatenation operation exceeds the maximum length of the data type resulting from the concatenation operation, the data cannot be concatenated.

10. If a trailing half-width space is stored at the end of the CHAR data, that half-width space 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

    "C1" + "C2" → 'ABCΔΔXYZ'

    "C2" + "C1" → 'XYZABCΔΔ'

    Legend: Δ: Half-width space

To Page Top

(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'

To Page Top