The concatenation operator (CONCAT) links two string operands to form a string expression.
The operands of concatenation must be compatible strings. Note that a binary string cannot be concatenated with a character string, including character strings defined as FOR BIT DATA (SQLSTATE 42884). For more information on compatibility, refer to the compatibility matrix on page Table 7.
If either operand can be null, the result can be null, and if either is null, the result is the null value. Otherwise, the result consists of the first operand string followed by the second. Note that no check is made for improperly formed mixed data when doing concatenation.
The length of the result is the sum of the lengths of the operands.
The data type and length attribute of the result is determined from that of
the operands as shown in the following table:
Table 10. Data Type and Length of Concatenated Operands
| Operands | Combined Length Attributes | Result |
|---|---|---|
| CHAR(A) CHAR(B) | <255 | CHAR(A+B) |
| CHAR(A) CHAR(B) | >254 | VARCHAR(A+B) |
| CHAR(A) VARCHAR(B) | <4001 | VARCHAR(A+B) |
| CHAR(A) VARCHAR(B) | >4000 | LONG VARCHAR |
| CHAR(A) LONG VARCHAR | - | LONG VARCHAR |
|
|
|
|
| VARCHAR(A) VARCHAR(B) | <4001 | VARCHAR(A+B) |
| VARCHAR(A) VARCHAR(B) | >4000 | LONG VARCHAR |
| VARCHAR(A) LONG VARCHAR | - | LONG VARCHAR |
|
|
|
|
| LONG VARCHAR LONG VARCHAR | - | LONG VARCHAR |
|
|
|
|
| CLOB(A) CHAR(B) | - | CLOB(MIN(A+B, 2G)) |
| CLOB(A) VARCHAR(B) | - | CLOB(MIN(A+B, 2G)) |
| CLOB(A) LONG VARCHAR | - | CLOB(MIN(A+32K, 2G)) |
| CLOB(A) CLOB(B) | - | CLOB(MIN(A+B, 2G)) |
|
|
|
|
| GRAPHIC(A) GRAPHIC(B) | <128 | GRAPHIC(A+B) |
| GRAPHIC(A) GRAPHIC(B) | >127 | VARGRAPHIC(A+B) |
| GRAPHIC(A) VARGRAPHIC(B) | <2001 | VARGRAPHIC(A+B) |
| GRAPHIC(A) VARGRAPHIC(B) | >2000 | LONG VARGRAPHIC |
| GRAPHIC(A) LONG VARGRAPHIC | - | LONG VARGRAPHIC |
|
|
|
|
| VARGRAPHIC(A) VARGRAPHIC(B) | <2001 | VARGRAPHIC(A+B) |
| VARGRAPHIC(A) VARGRAPHIC(B) | >2000 | LONG VARGRAPHIC |
| VARGRAPHIC(A) LONG VARGRAPHIC | - | LONG VARGRAPHIC |
|
|
|
|
| LONG VARGRAPHIC LONG VARGRAPHIC | - | LONG VARGRAPHIC |
|
|
|
|
| DBCLOB(A) GRAPHIC(B) | - | DBCLOB(MIN(A+B, 1G)) |
| DBCLOB(A) VARGRAPHIC(B) | - | DBCLOB(MIN(A+B, 1G)) |
| DBCLOB(A) LONG VARGRAPHIC | - | DBCLOB(MIN(A+16K, 1G)) |
| DBCLOB(A) DBCLOB(B) | - | DBCLOB(MIN(A+B, 1G)) |
|
|
|
|
| BLOB(A) BLOB(B) | - | BLOB(MIN(A+B, 2G)) |
Note that, for compatibility with previous versions, there is no automatic escalation of results involving LONG data types to LOB data types. For example, concatenation of a CHAR(200) value and a completely full LONG VARCHAR value would result in an error rather than in a promotion to a CLOB data type.
The code page of the result is considered a derived code page and is determined by the code page of its operands as explained in Rules for String Conversions.
One operand may be a parameter marker. If a parameter marker is used, then the data type and length attributes of that operand are considered to be the same as those for the non-parameter marker operand. The order of operations must be considered to determine these attributes in cases with nested concatenation.
Example 1: If FIRSTNME is Pierre and LASTNAME is Fermat, then the following :
FIRSTNME CONCAT ' ' CONCAT LASTNAME
returns the value Pierre Fermat
Example 2: Given:
The value of: COLA CONCAT :host_var CONCAT COLC CONCAT COLD is:
'AABB CC DDDDD'
The data type is VARCHAR, the length attribute is 17 and the result code page is the database code page.
Example 3: Given:
The parameter marker in the expression:
COLA CONCAT COLB CONCAT ?
is considered VARCHAR(15) since COLA CONCAT COLB is evaluated first giving a result which is the first operand of the second CONCAT operation.
A user-defined type cannot be used with the concatenation operator even if its source data type is character. To concatenate, create a function with the CONCAT operator as its source. For example, if there were distinct types TITLE and TITLE_DESCRIPTION, both of which had VARCHAR(25) data types, then the following user-defined function, ATTACH, could be used to concatenate them.
CREATE FUNCTION ATTACH (TITLE, TITLE_DESCRIPTION)
RETURNS VARCHAR(50) SOURCE CONCAT (VARCHAR(), VARCHAR())
Alternately, the concatenation operator could be overloaded using a user-defined function to add the new data types.
CREATE FUNCTION CONCAT (TITLE, TITLE_DESCRIPTION)
RETURNS VARCHAR(50) SOURCE CONCAT (VARCHAR(), VARCHAR())
If arithmetic operators are used, the result of the expression is a value derived from the application of the operators to the values of the operands.
If any operand can be null, or the database is configured with DFT_SQLMATHWARN set to yes, the result can be null.
If any operand has the null value, the result of the expression is the null value.
Arithmetic operators must not be applied to character strings. For example, USER+2 is invalid.
The prefix operator + (unary plus) does not change its operand. The prefix operator - (unary minus) reverses the sign of a nonzero operand; and if the data type of A is small integer, then the data type of -A is large integer. The first character of the token following a prefix operator must not be a plus or minus sign.
The infix operators +, -, *, and / specify addition, subtraction, multiplication, and division, respectively. The value of the second operand of division must not be zero. These operators can also be treated as functions. Thus, the expression "+"(a,b) is equivalent to the expression a+b. "operator" function.
If an arithmetic error such as zero divide or a numeric overflow occurs during the processing of an expression, an error is returned and the SQL statement processing the expression fails with an error (SQLSTATE 22003 or 22012).
A database can be configured (using DFT_SQLMATHWARN set to yes) so that arithmetic errors return a null value for the expression, issue a warning (SQLSTATE 01519 or 01564), and proceed with processing of the SQL statement. When arithmetic errors are treated as nulls, there are implications on the results of SQL statements. The following are some examples of these implications.
If these types of impacts are not acceptable, additional steps should be taken to handle the arithmetic error to produce acceptable results. Some examples are:
check (c1*c2 is not null and c1*c2>5000)
to cause the constraint to be violated on an overflow).
If both operands of an arithmetic operator are integers, the operation is performed in binary and the result is a large integer unless either (or both) operand is a big integer, in which case the result is a big integer. Any remainder of division is lost. The result of an integer arithmetic operation (including unary minus) must be within the range of the result type.
If one operand is an integer and the other is a decimal, the operation is performed in decimal using a temporary copy of the integer which has been converted to a decimal number with precision p and scale 0. p is 19 for a big integer, 11 for a large integer and 5 for a small integer.
If both operands are decimal, the operation is performed in decimal. The result of any decimal arithmetic operation is a decimal number with a precision and scale that are dependent on the operation and the precision and scale of the operands. If the operation is addition or subtraction and the operands do not have the same scale, the operation is performed with a temporary copy of one of the operands. The copy of the shorter operand is extended with trailing zeros so that its fractional part has the same number of digits as the longer operand.
The result of a decimal operation must not have a precision greater than 31. The result of decimal addition, subtraction, and multiplication is derived from a temporary result which may have a precision greater than 31. If the precision of the temporary result is not greater than 31, the final result is the same as the temporary result.
The following formulas define the precision and scale of the result of decimal operations in SQL. The symbols p and s denote the precision and scale of the first operand, and the symbols p' and s' denote the precision and scale of the second operand.
The precision is min(31,max(p-s,p'-s') +max(s,s')+1). The scale of the result of addition and subtraction is max (s,s').
The precision of the result of multiplication is min (31,p+p') and the scale is min(31,s+s').
The precision of the result of division is 31. The scale is 31-p+s-s'. The scale must not be negative.
If either operand of an arithmetic operator is floating-point, the operation is performed in floating-point, the operands having first been converted to double-precision floating-point numbers, if necessary. Thus, if any element of an expression is a floating-point number, the result of the expression is a double-precision floating-point number.
An operation involving a floating-point number and an integer is performed with a temporary copy of the integer which has been converted to double-precision floating-point. An operation involving a floating-point number and a decimal number is performed with a temporary copy of the decimal number which has been converted to double-precision floating-point. The result of a floating-point operation must be within the range of floating-point numbers.
A user-defined type cannot be used with arithmetic operators even if its source data type is numeric. To perform an arithmetic operation, create a function with the arithmetic operator as its source. For example, if there were distinct types INCOME and EXPENSES, both of which had DECIMAL(8,2) data types, then the following user-defined function, REVENUE, could be used to subtract one from the other.
CREATE FUNCTION REVENUE (INCOME, EXPENSES)
RETURNS DECIMAL(8,2) SOURCE "-" (DECIMAL, DECIMAL)
Alternately, the - (minus) operator could be overloaded using a user-defined function to subtract the new data types.
CREATE FUNCTION "-" (INCOME, EXPENSES)
RETURNS DECIMAL(8,2) SOURCE "-" (DECIMAL, DECIMAL)
Datetime values can be incremented, decremented, and subtracted. These operations may involve decimal numbers called durations. Following is a definition of durations and a specification of the rules for datetime arithmetic.
A duration is a number representing an interval of time. There are four types of durations: