Concatenate Function

Concatenate Function

Sometimes it is necessary to combine together (concatenate) the results from several different fields. Each database provides a way to do this:

CONCAT(str1, str2, str3, ...): Concatenate str1, str2, str3, and any other strings together. Please note the Oracle CONCAT() function only allows two arguments -- only two strings can be

put together at a time using this function. However, it is possible to concatenate more than two strings at a time in Oracle using '||'.

Let's look at some examples. Assume we have the following table:
Employees

SQL> desc employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

create or replace function mycat(f varchar2,l varchar2)
return varchar2
is
begin
return f||'  '||l;
end;
/
Function created.

SQL> select mycat(first_name,last_name)
  2  from employees
  3  where salary>10000;


Output:-
MYCAT(FIRST_NAME,LAST_NAME)
----------------------------------
Michael  Hartstein
Shelley  Higgins
Neena  Kochhar
Lex  De Haan
Nancy  Greenberg
Den  Raphaely
John  Russell
Karen  Partners
Alberto  Errazuriz
Gerald  Cambrault
Eleni  Zlotkey