All Products
Document Center

Concatenation operators

Last Updated: Jun 18, 2021

The concatenation operator || is used to concatenate strings and data of the CLOB type.

After two strings are concatenated, another string is generated. If the data types of the two strings are CHAR, the returned result is of the CHAR data type and can be at most 2,000 characters in length. If the data type of one of the concatenated strings is VARCHAR2, the returned result is of the VARCHAR2 data type and can be at most 4,000 characters in length. If one of the parameters on the two sides of the concatenation operator is a value of the CLOB data type, the returned result is of a temporary CLOB data type. The trailing spaces in the strings are preserved by concatenation, regardless of the data types of the parameters on both sides of the concatenation operator.

Although ApsaraDB for OceanBase regards a zero-length string as NULL, an operand that contains values is generated after an operand that contains values is concatenated with another zero-length string. Therefore, NULL can be generated only if two NULL strings are concatenated. However, this may not continue to be true in future ApsaraDB for OceanBase database versions. To concatenate expressions that may be NULL, use the NVL function to directly convert the expressions to zero-length strings.

In the following example, a table that contains columns of the CHAR and VARCHAR2 types is created. Values that have and do not have trailing spaces are inserted and concatenated.

CREATE TABLE tab1 (col1 VARCHAR2(6), col2 CHAR(10), col3 VARCHAR2(10), col4 CHAR(6));
INSERT INTO tab1 (col1, col2, col3, col4) VALUES ('abc', 'def    ', 'ghi    ', 'jkl');
SELECT col1 || col2 || col3 || col4 "Concatenation" FROM tab1;

The following result is returned:

| Concatenation              |
| abcdef       ghi    jkl    |