SQL | Concatenation Operator
Last Updated :
21 Mar, 2018
Prerequisite: Basic Select statement, Insert into clause, SQL Create Clause, SQL Aliases
|| or concatenation operator is use to link columns or character strings. We can also use a literal. A literal is a character, number or date that is included in the SELECT statement.
Let’s demonstrate it through an example:
Syntax:
SELECT id, first_name, last_name, first_name || last_name,
salary, first_name || salary FROM myTable
Output (Third and Fifth Columns show values concatenated by operator ||)
id first_name last_name first_name||last_name salary first_name||salary
1 Rajat Rawat RajatRawat 10000 Rajat10000
2 Geeks ForGeeks GeeksForGeeks 20000 Geeks20000
3 Shane Watson ShaneWatson 50000 Shane50000
4 Kedar Jadhav KedarJadhav 90000 Kedar90000
Note: Here above we have used || which is known as Concatenation operator which is used to link 2 or as many columns as you want in your select query and it is independent of the datatype of column. Here above we have linked 2 columns i.e, first_name+last_name as well as first_name+salary.
We can also use literals in the concatenation operator. Let’s see:
Example 1: Using character literal
Syntax:
SELECT id, first_name, last_name, salary,
first_name||' has salary '||salary as "new" FROM myTable
Output : (Concatenating three values and giving a name 'new')
id first_name last_name salary new
1 Rajat Rawat 10000 Rajat has salary 10000
2 Geeks ForGeeks 20000 Geeks has salary 20000
3 Shane Watson 50000 Shane has salary 50000
4 Kedar Jadhav 90000 Kedar has salary 90000
Note: Here above we have used has salary as a character literal in our select statement. Similarly we can use number literal or date literal according to our requirement.
Example 2: Using character as well as number literal
Syntax:
SELECT id, first_name, last_name, salary, first_name||100||'
has id '||id AS "new" FROM myTable
Output (Making readable output by concatenating a string
with values)
id first_name last_name salary new
1 Rajat Rawat 10000 Rajat100 has id 1
2 Geeks ForGeeks 20000 Geeks100 has id 2
3 Shane Watson 50000 Shane100 has id 3
4 Kedar Jadhav 90000 Kedar100 has id 4
Here above we have used has salary as a character literal as well as 100 as number literal in our select statement.
References:
1) About Concatenation operator: Oracle Docs
2) Performing SQL Queries Online: Oracle Live SQL
Note: For performing SQL Queries online you must have account on Oracle, if you don’t have then you can make by opening above link.
Please Login to comment...