Concatenation Operator in Oracle

•The concatenation operator concatenates operands.

•This operator is expressed by (double vertical lines) ||.

•This operator operates on character strings and CLOB datatype.

•This operator behaves as following way,

If both operands are of datatype CHAR, the result holds CHAR datatype and is limited to 2000 characters. If either string has datatype VARCHAR2, the result holds datatype VARCHAR2 and is limited to 4000 characters. If either operand is has datatype CLOB, the result become temporary CLOB.

•Trailing or leading blanks are preserved by this operator. Example:
SQL> select ' ' || 'test' from dual;
''||'TEST'
-----------------
test

SQL> select length(' ' || 'test ') from dual;
LENGTH(''||'TEST')
------------------
18

•Instead of || the CONCAT function also be used for concatenation.

Example:
-----------------
SQL> create table test_concat(col1 varchar2(10), col2 varchar2(30));
Table created.

SQL> insert into test_concat values('Arju ','Bangladesh India Usa');
1 row created.

SQL> select concat(col1,col2) from test_concat;
CONCAT(COL1,COL2)
----------------------------------------
Arju Bangladesh India Usa

SQL> select col1||col2 from test_concat;
COL1||COL2
----------------------------------------
Arju Bangladesh India Usa

SQL> select col1||'-'||'Three Countries: '||col2 from test_concat;
COL1||'-'||'THREECOUNTRIES:'||COL2
----------------------------------------------------------
Arju -Three Countries: Bangladesh India Usa

Related Documents:
--------------------------------------
Types of Operator in Oracle