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

Arithmatic Operators in Oracle

Arithmetic operators in oracle can be used to add, subtract, multiply, divide and negate numeric values. The + and - arithmetic operators can also be used in datetime and interval arithmetics.

Based on the function we can divide the arithmetic operators in the following types.

1)Unary + and - Operator:
-----------------------------------------------
•When + or - denote positive or negative expression then they are called unary operators.

•To represent positive or negative expression the + an - operator appear just before operand.

•Example of unary operator:

Select col1 from table1 where col1>-1;

Select col1 from table1 where -col1>1;

Here - operator used as unary operator.

2)Binary + and - Operator:
---------------------------------------------
•When + and - are used to add or subtract operands then they are called binary operators.

•They appear between operands.

•Example of binary operator:

Select col1 from table1 where col1+col2>10;

3)Binary * and / Operator:
------------------------------------------------
•The * operator is used to multiply operands and / is used for divide.

•Both are binary operators.

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

Types of Operators in Oracle

In oracle an operator manipulates data items and return a result after manipulation. The data items on which it operates are called operands. Based on the operator function it can be divided into following types.

1)User-Defined Operators: Discussed in

2)Arithmetic Operators: Discussed in Arithmatic Operators

3)Concatenation Operator: Discussed in Concatenation Operator.

4)Hierarchical Query Operators: Discussed in

5)Set Operators: Discussed in

6)Multiset Operators: Discussed in

An operator can appear before an operand, after an operand or between operands.