Tagicon

Use of Concatenation Operator in SQL

Posted by tagicon_admin | in : SQL

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

 

Leave a Reply

Follow by Email
YouTube
Pinterest
LinkedIn
Share
Instagram