|Oracle8i SQL Reference
Release 2 (8.1.6)
Operators, 5 of 10
The concatenation operator manipulates character strings. Table 3-3 describes the concatenation operator.
Concatenates character strings.
The result of concatenating two character strings is another character string. If both character strings are of datatype
CHAR, the result has datatype
CHAR and is limited to 2000 characters. If either string is of datatype
VARCHAR2, the result has datatype
VARCHAR2 and is limited to 4000 characters. Trailing blanks in character strings are preserved by concatenation, regardless of the strings' datatypes.
On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle environment. Oracle provides the
CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets.
Although Oracle treats zero-length character strings as nulls, concatenating a zero-length character string with another operand always results in the other operand, so null can result only from the concatenation of two null strings. However, this may not continue to be true in future versions of Oracle. To concatenate an expression that might be null, use the
NVL function to explicitly convert the expression to a zero-length string.
"Character Datatypes" for more information on the differences between the
This example creates a table with both
VARCHAR2 columns, inserts values both with and without trailing blanks, and then selects these values and concatenates them. Note that for both
VARCHAR2 columns, the trailing blanks are preserved.
CREATE TABLE tab1 (col1 VARCHAR2(6), col2 CHAR(6), col3 VARCHAR2(6), col4 CHAR(6) ); Table created. INSERT INTO tab1 (col1, col2, col3, col4) VALUES ('abc', 'def ', 'ghi ', 'jkl'); 1 row created. SELECT col1||col2||col3||col4 "Concatenation" FROM tab1; Concatenation ------------------------ abcdef ghi jkl