The concatenation operator (||) concatenates strings and CLOB data. A new string is obtained after you concatenate two strings by using the concatenation operator.
If both strings are of the CHAR data type, the result is also of the CHAR data type and cannot exceed 2,000 characters in length.
If either string is of the VARCHAR2 data type, the result is also of the VARCHAR2 data type and cannot exceed 4,000 characters in length.
If either parameter to the concatenation operator is of the CLOB data type, the return type is temporary CLOB.
Although OceanBase Database treats a zero-length string as a null, concatenating an operand with another zero-length string results in an operand. Therefore, NULL can result only from the concatenation of two NULL strings. To concatenate an expression that might be null, you can use the NVL function to explicitly convert the expression to a zero-length string.
The following example creates a table that contains CHAR and VARCHAR2 columns, inserts values with and without trailing spaces to the columns, and concatenates two of the values.
obclient> CREATE TABLE tbl1 (col1 VARCHAR2(6), col2 CHAR(10), col3 VARCHAR2(10), col4 CHAR(6));
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 (col1, col2, col3, col4) VALUES ('abc', 'def ', 'ghi ', 'jkl');
Query OK, 1 row affected
obclient> SELECT col1 || col2 || col3 || col4 "Concatenation" FROM tbl1;
+----------------------------+
| Concatenation |
+----------------------------+
| abcdef ghi jkl |
+----------------------------+
1 row in set