2.13 Concatenation operation

Organization of this section
(1) Function
(2) Format
(3) Rules

(1) Function

When you specify a concatenation operation in a value expression, HiRDB concatenates multiple data strings (character strings or binary columns) in the specified order to create a single data string.

Note that you can specify concatenation operations on BLOB type data, and on BINARY type data that has a maximum length of 32,001 bytes or greater, only as an update value in the SET clause of the UPDATE statement. For details, see Rules on updating a column of the BLOB type or the BINARY type with a definition length of 32,001 bytes or greater, using concatenation operations under UPDATE statement Format 1 (Update data) in Chapter 4. The format is the same as that for concatenation operations on character string data.

The following table shows the data types that can be concatenated and the data types resulting from concatenation operations for character string data and for BINARY type data that has a maximum length of no more than 32,000 bytes.

Table 2-18 Data types eligible for concatenation and the data type of a concatenation operation result

Data type of operand 1Data type of operand 2
Character dataNational character dataMixed character dataBinary data
CHARVARCHARNCHARNVARCHARMCHARMVRCHAR
Char dataCHARCHAR1VARCHARNNMCHAR1MVARCHARBINARY
VARCHARVARCHARVARCHARNNMVARCHARMVARCHARN
Nat'l char dataNCHARNNNCHAR1NVARCHARNNBINARY2
NVARCHARNNNVARCHARNVARCHARNNN
Mixed char dataMCHARMCHAR1MVARCHARNNMCHAR1MVARCHARN
MVARCHARMVARCHARMVARCHARNNMVARCHARMVARCHARN
Binary dataBINARYNBINARY2NNNNBINARY
N: Cannot be specified.
Char: Character
Nat'l: National
1 If the result length after concatenation is greater than the maximum length of CHAR, NCHAR or MCHAR, the result is treated as either VARCHAR, NVARCHAR or MVARCHAR.
2 For the BINARY type, only a hexadecimal character string literal can be specified in the operation term.

Table 2-19 Data length of the result of concatenation

Data type of result of concatenationData length (maximum length for variable-length data)
CHAR(n)n = n1 + n2 (if n > 255, the data type of the result is VARCHAR)
VARCHAR(n)n = n1 + n2 (if n > 32000, an error results)
NCHAR(n)n = n1 + n2 (if n > 127, the data type of the result is NVARCHAR)
NVARCHAR(n)n = n1 + n2 (if n > 16000, an error results)
MCHAR(n)n = n1 + n2 (if n > 255, the data type of the result is MVARCHAR)
MVARCHAR(n)n = n1 + n2 (if n > 32000, an error results)
BINARY(n)n = n1 + n2 (provided n > 32,000; an error in clauses other than the SET clause of the UPDATE statement)

n1: Data length of operand 1

n2: Data length of operand 2

Note 1: n is expressed in bytes for data types CHAR, VARCHAR, MCHAR, MVARCHAR, and BINARY. For NCHAR and NVARCHAR, n is expressed as the number of characters.

Note 2: If operands 1 and 2 are character string literals (including national and mixed character string literals) whose length is 0, n1 or n2 must be set to 0. However, if the data length of the concatenation result is 0, n is 1.

(2) Format

value-expression | | primary

(3) Rules

For concatenation operations on character string data, also see the common rules given in 2.9 Value expressions, value specifications, and item specifications.

  1. An embedded variable or a ? parameter cannot be directly specified in an operand of a concatenation operation.
  2. The null value is allowed in the result of a concatenation operation, regardless of the NOT NULL constraint on primaries or value expressions.
  3. An error results if the data length (maximum length) of the result of a concatenation operation exceeds the allowable maximum length for variable-length data.
  4. If the data type of the result of a concatenation operation is the BINARY type and the data length (maximum length) of the result is greater than 32,000 bytes, an error may occur in clauses other than the SET clause of the UPDATE statement.