SQL | Concatenation Operator

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.