Concatenation operators

The concatenation operator || is used to concatenate strings and CLOB data types. When you use the concatenation operator to concatenate two strings, you will get another string.

If both strings are of the CHAR data type, the result is also of the CHAR data type, and the maximum length is 2000 characters.

If either of the strings is of the VARCHAR2 data type, the result is also of the VARCHAR2 data type, and the maximum length is 4000 characters.

If either of the operands on either side of the concatenation operator is a CLOB value, the result is a temporary CLOB.

Although OceanBase Database treats a string of length 0 as a NULL value, concatenating a non-NULL value with a string of length 0 results in a non-NULL value. Therefore, a NULL value can only be produced by concatenating two NULL strings. To concatenate expressions that may be NULL, it is recommended to use the NVL function to convert the expressions directly to strings of length 0.

The following example creates a table with both CHAR and VARCHAR2 columns, inserts values with and without trailing spaces, and concatenates them.

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