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 about concatenating BLOB type or BINARY type data with a defined length of 32,001 bytes or greater, see Rules on updating a column of the BLOB type or the BINARY type with a defined 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 lists 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-21 Data types eligible for concatenation and the data type of a concatenation operation result (1/2)

Data type of operand 1Data type of operand 2
Character dataNational character dataMixed character dataBinary data
CHARVARCHARNCHARNVARCHARMCHARMVRCHAR
Char dataCHARCHAR#1VARCHARNNMCHAR#1MVARCHARBINARY
VARCHARVARCHARVARCHARNNMVARCHARMVARCHARN
Nat'l char dataNCHARNNNCHAR#1NVARCHARNNBINARY#2
NVARCHARNNNVARCHARNVARCHARNNN
Mixed char dataMCHARMCHAR#1MVARCHARNNMCHAR#1MVARCHARN
MVARCHARMVARCHARMVARCHARNNMVARCHARMVARCHARN
Binary dataBINARYNBINARY#2NNNNBINARY
Numeric data#3, #4INTEGER, SMALLINT, or DECIMALVARCHARVARCHARNNMVARCHARMVARCHARN
FLOAT or SMALLFLTCHAR#1VARCHARNNMCHAR#1MVARCHARN
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.
#3
Numeric data is converted as follows:
  • INTEGER type numeric data is converted to VARCHAR(11) character string data.
  • SMALLINT type numeric data is converted to VARCHAR(6) character string data.
  • DECIMAL(p,0) type numeric data is converted to VARCHAR(p+1) character string data.
  • DECIMAL(p,s) type numeric data is converted to VARCHAR(p+2) character string data.
  • FLOAT or SMALLFLT type numeric data is converted to CHAR(23) character string data.
#4
When numeric data is concatenated, the numeric data is converted as follows:
  • If numeric data and character string data are concatenated, numeric data is converted to the character set of the character string data.
  • If numeric data and mixed character string data are concatenated, numeric data is converted to the character set of the mixed character string data.
  • If numeric data and numeric data are concatenated, numeric data is converted to the default character set.

    Table 2-22 Data types eligible for concatenation and the data type of a concatenation operation result (2/2)

    First operand data typeSecond operand data type
    Numeric data#2, #3
    INTEGER, SMALLINT, and DECIMALFLOAT and SMALLFLT
    Character string dataCHARVARCHARCHAR#1
    VARCHARVARCHARVARCHAR
    National character dataNCHARNN
    NVARCHARNN
    Mixed character dataMCHARMVARCHARMCHAR#1
    MVARCHARMVARCHARMVARCHAR
    Binary dataBINARYNN
    Numeric data#2INTEGER, SMALLINT, and DECIMALVARCHARVARCHAR
    FLOAT and SMALLFLTVARCHARCHAR#1
Legend:
N: Cannot be specified.
#1
See footnote #1 following in Table 2-21 Data types eligible for concatenation and the data type of a concatenation operation result (1/2).
#2
See footnote #3 following Table 2-21 Data types eligible for concatenation and the data type of a concatenation operation result (1/2).
#3
See footnote #4 following Table 2-21 Data types eligible for concatenation and the data type of a concatenation operation result (1/2).

Table 2-23 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.
  5. If the character sets are different, you cannot perform concatenation operations on different character data. Concatenation operations can be performed, however, by converting the following value expression to the character set of the concatenation object:
    • Character string literal