Concatenation operator is used to links or add columns or character srtings to other columns or character. Concatenation
is represented by two vertical bars (||). When we use concatenatin operator, we can change a column heading by using a
column alias.
Syntax of Concatenation Operator:
=================================
string1 || string2 [ || string_n]
Here I am showing two example for different between SQL statement without concatenation operator and SQL statement with concatenation operator:
–> SQL statement without concatenation operator:
SELECT first_name, last_name FROM employees;
Above query will make the following result:
FIRST_NAME LAST_NAME -------------------- -------------- Donald OConnell Douglas Grant Jennifer Whalen Michael Hartstein Pat Fay Susan Mavris Hermann Baer Shelley Higgins William Gietz Steven King Neena Kochhar FIRST_NAME LAST_NAME -------------------- -------------- Lex De Haan Alexander Hunold Bruce Ernst David Austin Valli Pataballa Diana Lorentz Nancy Greenberg Daniel Faviet John Chen Ismael Sciarra Jose Manuel Urman FIRST_NAME LAST_NAME -------------------- -------------- Luis Popp Den Raphaely Alexander Khoo Shelli Baida Sigal Tobias Guy Himuro Karen Colmenares Matthew Weiss Adam Fripp Payam Kaufling Shanta Vollman FIRST_NAME LAST_NAME -------------------- -------------- Kevin Mourgos Julia Nayer Irene Mikkilineni James Landry Steven Markle Laura Bissot Mozhe Atkinson James Marlow TJ Olson Jason Mallin Michael Rogers FIRST_NAME LAST_NAME -------------------- -------------- Ki Gee Hazel Philtanker Renske Ladwig Stephen Stiles John Seo Joshua Patel Trenna Rajs Curtis Davies Randall Matos Peter Vargas John Russell FIRST_NAME LAST_NAME -------------------- -------------- Karen Partners Alberto Errazuriz Gerald Cambrault Eleni Zlotkey Peter Tucker David Bernstein Peter Hall Christopher Olsen Nanette Cambrault Oliver Tuvault Janette King
–>SQL statement with concatenation operator:
SELECT first_name ||' '||last_name FROM employees;
Above query will make the following result:
FIRST_NAME||''||LAST_NAME --------------------------------- Donald OConnell Douglas Grant Jennifer Whalen Michael Hartstein Pat Fay Susan Mavris Hermann Baer Shelley Higgins William Gietz Steven King Neena Kochhar FIRST_NAME||''||LAST_NAME --------------------------------- Lex De Haan Alexander Hunold Bruce Ernst David Austin Valli Pataballa Diana Lorentz Nancy Greenberg Daniel Faviet John Chen Ismael Sciarra Jose Manuel Urman FIRST_NAME||''||LAST_NAME --------------------------------- Luis Popp Den Raphaely Alexander Khoo Shelli Baida Sigal Tobias Guy Himuro Karen Colmenares Matthew Weiss Adam Fripp Payam Kaufling Shanta Vollman FIRST_NAME||''||LAST_NAME --------------------------------- Kevin Mourgos Julia Nayer Irene Mikkilineni James Landry Steven Markle Laura Bissot Mozhe Atkinson James Marlow TJ Olson Jason Mallin Michael Rogers FIRST_NAME||''||LAST_NAME --------------------------------- Ki Gee Hazel Philtanker Renske Ladwig Stephen Stiles John Seo Joshua Patel Trenna Rajs Curtis Davies Randall Matos Peter Vargas John Russell
–> SQL statement with concatenation operator and using column aliase to change the column heading
SELECT first_name ||' '||last_name "Name of Employee" FROM employees;
Above query will make the following result:
Name of Employee ------------------------- Donald OConnell Douglas Grant Jennifer Whalen Michael Hartstein Pat Fay Susan Mavris Hermann Baer Shelley Higgins William Gietz Steven King Neena Kochhar Name of Employee ------------------------- Lex De Haan Alexander Hunold Bruce Ernst David Austin Valli Pataballa Diana Lorentz Nancy Greenberg Daniel Faviet John Chen Ismael Sciarra Jose Manuel Urman Name of Employee ------------------------- Luis Popp Den Raphaely Alexander Khoo Shelli Baida Sigal Tobias Guy Himuro Karen Colmenares Matthew Weiss Adam Fripp Payam Kaufling Shanta Vollman Name of Employee ------------------------- Kevin Mourgos Julia Nayer Irene Mikkilineni James Landry Steven Markle Laura Bissot Mozhe Atkinson James Marlow TJ Olson Jason Mallin Michael Rogers Name of Employee ------------------------- Ki Gee Hazel Philtanker Renske Ladwig Stephen Stiles John Seo Joshua Patel Trenna Rajs Curtis Davies Randall Matos Peter Vargas John Russell
Now I am showing more example of concatenation operator below:
–> Concatenating two NUMBERS
SELECT 100 || 500 FROM dual;
Above query will make the following result:
100||5 ------ 100500
–> Retrieve string with single quote by using concatenation operator.
SELECT 'Let''s'||' '||'Talk'||' NASA''s Mission' "Easy Example" FROM dual;
Above query will make the following result:
Easy Example ------------------------- Let's Talk NASA's Mission
–>Retrieve department id, first name, job id, hire date, salary and employee id by using concatenation operator. Name the coloumn title Employees History.
SELECT department_id||' '|| first_name||' '|| job_id||' '|| hire_date||' '|| salary||' '|| employee_id "EmpHistory" FROM employees;
Above query will make the following result:
EmpHistory ---------------------------------------------- 50 Donald SH_CLERK 21-JUN-07 2600 198 50 Douglas SH_CLERK 13-JAN-08 2600 199 10 Jennifer AD_ASST 17-SEP-03 4400 200 20 Michael MK_MAN 17-FEB-04 13000 201 20 Pat MK_REP 17-AUG-05 6000 202 40 Susan HR_REP 07-JUN-02 6500 203 70 Hermann PR_REP 07-JUN-02 10000 204 110 Shelley AC_MGR 07-JUN-02 12008 205 110 William AC_ACCOUNT 07-JUN-02 8300 206 90 Steven AD_PRES 17-JUN-03 24000 100 90 Neena AD_VP 21-SEP-05 17000 101 EmpHistory ---------------------------------------------- 90 Lex AD_VP 13-JAN-01 17000 102 60 Alexander IT_PROG 03-JAN-06 9000 103 60 Bruce IT_PROG 21-MAY-07 6000 104 60 David IT_PROG 25-JUN-05 4800 105 60 Valli IT_PROG 05-FEB-06 4800 106 60 Diana IT_PROG 07-FEB-07 4200 107 100 Nancy FI_MGR 17-AUG-02 12008 108 100 Daniel FI_ACCOUNT 16-AUG-02 9000 109 100 John FI_ACCOUNT 28-SEP-05 8200 110 100 Ismael FI_ACCOUNT 30-SEP-05 7700 111 100 Jose Manuel FI_ACCOUNT 07-MAR-06 7800 112 EmpHistory ---------------------------------------------- 100 Luis FI_ACCOUNT 07-DEC-07 6900 113 30 Den PU_MAN 07-DEC-02 11000 114 30 Alexander PU_CLERK 18-MAY-03 3100 115 30 Shelli PU_CLERK 24-DEC-05 2900 116 30 Sigal PU_CLERK 24-JUL-05 2800 117 30 Guy PU_CLERK 15-NOV-06 2600 118 30 Karen PU_CLERK 10-AUG-07 2500 119 50 Matthew ST_MAN 18-JUL-04 8000 120 50 Adam ST_MAN 10-APR-05 8200 121 50 Payam ST_MAN 01-MAY-03 7900 122 50 Shanta ST_MAN 10-OCT-05 6500 123 EmpHistory ---------------------------------------------- 50 Kevin ST_MAN 16-NOV-07 5800 124 50 Julia ST_CLERK 16-JUL-05 3200 125 50 Irene ST_CLERK 28-SEP-06 2700 126 50 James ST_CLERK 14-JAN-07 2400 127 50 Steven ST_CLERK 08-MAR-08 2200 128 50 Laura ST_CLERK 20-AUG-05 3300 129 50 Mozhe ST_CLERK 30-OCT-05 2800 130 50 James ST_CLERK 16-FEB-05 2500 131 50 TJ ST_CLERK 10-APR-07 2100 132 50 Jason ST_CLERK 14-JUN-04 3300 133 50 Michael ST_CLERK 26-AUG-06 2900 134 EmpHistory ---------------------------------------------- 50 Ki ST_CLERK 12-DEC-07 2400 135 50 Hazel ST_CLERK 06-FEB-08 2200 136 50 Renske ST_CLERK 14-JUL-03 3600 137 50 Stephen ST_CLERK 26-OCT-05 3200 138 50 John ST_CLERK 12-FEB-06 2700 139 50 Joshua ST_CLERK 06-APR-06 2500 140 50 Trenna ST_CLERK 17-OCT-03 3500 141 50 Curtis ST_CLERK 29-JAN-05 3100 142 50 Randall ST_CLERK 15-MAR-06 2600 143 50 Peter ST_CLERK 09-JUL-06 2500 144 80 John SA_MAN 01-OCT-04 14000 145
–> Retrieve multiple column data with string by using concatenation operator.
SELECT 'The department id of Mr.'|| first_name||' '||'is'||' '|| department_id "Dept Information" FROM employees;
Above query will make the following result:
Dept Information -------------------------------------- The department id of Mr.Donald is 50 The department id of Mr.Douglas is 50 The department id of Mr.Jennifer is 10 The department id of Mr.Michael is 20 The department id of Mr.Pat is 20 The department id of Mr.Susan is 40 The department id of Mr.Hermann is 70 The department id of Mr.Shelley is 110 The department id of Mr.William is 110 The department id of Mr.Steven is 90 The department id of Mr.Neena is 90 Dept Information -------------------------------------- The department id of Mr.Lex is 90 The department id of Mr.Alexander is 6 The department id of Mr.Bruce is 60 The department id of Mr.David is 60 The department id of Mr.Valli is 60 The department id of Mr.Diana is 60 The department id of Mr.Nancy is 100 The department id of Mr.Daniel is 100 The department id of Mr.John is 100 The department id of Mr.Ismael is 100 The department id of Mr.Jose Manuel is Dept Information -------------------------------------- The department id of Mr.Luis is 100 The department id of Mr.Den is 30 The department id of Mr.Alexander is 3 The department id of Mr.Shelli is 30 The department id of Mr.Sigal is 30 The department id of Mr.Guy is 30 The department id of Mr.Karen is 30 The department id of Mr.Matthew is 50 The department id of Mr.Adam is 50 The department id of Mr.Payam is 50 The department id of Mr.Shanta is 50 Dept Information -------------------------------------- The department id of Mr.Kevin is 50 The department id of Mr.Julia is 50 The department id of Mr.Irene is 50 The department id of Mr.James is 50 The department id of Mr.Steven is 50 The department id of Mr.Laura is 50 The department id of Mr.Mozhe is 50 The department id of Mr.James is 50 The department id of Mr.TJ is 50 The department id of Mr.Jason is 50 The department id of Mr.Michael is 50 Dept Information -------------------------------------- The department id of Mr.Ki is 50 The department id of Mr.Hazel is 50 The department id of Mr.Renske is 50 The department id of Mr.Stephen is 50 The department id of Mr.John is 50 The department id of Mr.Joshua is 50 The department id of Mr.Trenna is 50 The department id of Mr.Curtis is 50 The department id of Mr.Randall is 50 The department id of Mr.Peter is 50 The department id of Mr.John is 80