Skip to content
Related Articles

Related Articles

Save Article
Improve Article
Save Article
Like Article

SQL | Concatenation Operator

  • Difficulty Level : Medium
  • 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.

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

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.

My Personal Notes arrow_drop_up
Recommended Articles
Page :