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

To Page Top

(3) Types of concatenation operations

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

Table 7‒34: 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. The first and second operands must both be either character string data or binary data.

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

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

4. You cannot specify a dynamic parameter by itself for the first operand or second operand.

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

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

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

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

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