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 1 | Data type of operand 2 | |||||||
---|---|---|---|---|---|---|---|---|
Character data | National character data | Mixed character data | Binary data | |||||
CHAR | VARCHAR | NCHAR | NVARCHAR | MCHAR | MVRCHAR | |||
Char data | CHAR | CHAR#1 | VARCHAR | N | N | MCHAR#1 | MVARCHAR | BINARY |
VARCHAR | VARCHAR | VARCHAR | N | N | MVARCHAR | MVARCHAR | N | |
Nat'l char data | NCHAR | N | N | NCHAR#1 | NVARCHAR | N | N | BINARY#2 |
NVARCHAR | N | N | NVARCHAR | NVARCHAR | N | N | N | |
Mixed char data | MCHAR | MCHAR#1 | MVARCHAR | N | N | MCHAR#1 | MVARCHAR | N |
MVARCHAR | MVARCHAR | MVARCHAR | N | N | MVARCHAR | MVARCHAR | N | |
Binary data | BINARY | N | BINARY#2 | N | N | N | N | BINARY |
Numeric data#3, #4 | INTEGER, SMALLINT, or DECIMAL | VARCHAR | VARCHAR | N | N | MVARCHAR | MVARCHAR | N |
FLOAT or SMALLFLT | CHAR#1 | VARCHAR | N | N | MCHAR#1 | MVARCHAR | N |
Table 2-22 Data types eligible for concatenation and the data type of a concatenation operation result (2/2)
First operand data type | Second operand data type | ||
---|---|---|---|
Numeric data#2, #3 | |||
INTEGER, SMALLINT, and DECIMAL | FLOAT and SMALLFLT | ||
Character string data | CHAR | VARCHAR | CHAR#1 |
VARCHAR | VARCHAR | VARCHAR | |
National character data | NCHAR | N | N |
NVARCHAR | N | N | |
Mixed character data | MCHAR | MVARCHAR | MCHAR#1 |
MVARCHAR | MVARCHAR | MVARCHAR | |
Binary data | BINARY | N | N |
Numeric data#2 | INTEGER, SMALLINT, and DECIMAL | VARCHAR | VARCHAR |
FLOAT and SMALLFLT | VARCHAR | CHAR#1 |
Table 2-23 Data length of the result of concatenation
Data type of result of concatenation | Data 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.
value-expression | | primary
For concatenation operations on character string data, also see the common rules given in 2.9 Value expressions, value specifications, and item specifications.