Let Us Learn Oracle - Part 1 of N [ String Concatenation in Oracle ]
Source table: tblPlayers
In Oracle concatenation can be done in the following ways
Option 1: Using Concatenation Operator ( || )
SQL> Select PlayerFirstName || ' ' || PlayerLastName FullName From tblPlayers;
FULLNAME
---------------------
A Raman
B Kadir
C Nadir
D Das
E Punchkar
F Fateman
G Gajani
H Hariharan
I Ikat
J Jwar
10 rows selected.
Option 2: Using CONCAT function
SQL> Select Concat(PlayerFirstName,PlayerLastName) FullName From tblPlayers;
FULLNAME
--------------------
ARaman
BKadir
CNadir
DDas
EPunchkar
FFateman
GGajani
HHariharan
IIkat
JJwar
10 rows selected.
However, this function has a limitation as it cannot accept more than 2 arguments. Infact, it accepts precisely 2 arguments.Henceforth, the below statement will fail
SQL> Select Concat(PlayerFirstName, ' ',PlayerLastName) FullName From tblPlayers;
Select Concat(PlayerFirstName, ' ',PlayerLastName) FullName From tblPlayers
*
ERROR at line 1:
ORA-00909: invalid number of arguments
But we can culminate this by "Nesting" the CONCAT function as shown under
SQL> Select Concat(PlayerFirstName,Concat(' ' ,PlayerLastName))FullName From tblPlayers;
FULLNAME
---------------------
A Raman
B Kadir
C Nadir
D Das
E Punchkar
F Fateman
G Gajani
H Hariharan
I Ikat
J Jwar
10 rows selected.
N.B.~Unlike Sql Server, we cannot use the '+' operator here for concatenation. It will throw error
SQL> Select PlayerFirstName + ' ' + PlayerLastName FullName From tblPlayers;
Select PlayerFirstName + ' ' + PlayerLastName FullName From tblPlayers
*
ERROR at line 1:
ORA-01722: invalid number
Concatenation in Sql Server
Before Denali, we had only one way i.e. by using '+' operator
Select PlayerFirstName + ' ' + PlayerLastName FullName From tblPlayers
But with the advent of Denali CTP3, we can use the Concat function also as
Select Concat(PlayerFirstName,' ' ,PlayerLastName)FullName From tblPlayers
Thanks for reading