SQL CONCAT Function

The CONCAT function in SQL is a String function, which is used to merge two or more strings. The Concat service converts the Null values to an Empty string when we display the result. This function is used to concatenate two strings to make a single string. The operator is used to link character strings and column string.

We can use a literal in CONCAT Function. A literal is a number, character, or date that includes the SELECT statement.

Syntax of CONCAT function:

SELECT CONCAT (String 1, String 2, String3.., String N)  

FROM [Source] 

Example-

SQL>   SELECT CONCAT ('FIRST', 'SECOND');  

CONCAT(‘ FIRST’,’SECOND’)

FIRST SECOND

To understand the CONCAT function in detail, consider an employee_tbl table, which has the following records –

SQL> SELECT * FROM employee_ tbl ;   

ID	NAME	WORK_DATE	DAILY_TYPING_PAGES
1	Michal	2009-02-15	270
2	Zeena	2003-03-24	250
2	kachner	2007-08-19	277
2	warner	2007-04-25	264
3	Joy	2007-05-17	250
4	atire	2006-06-23	270
5	delph	2004-05-28	230

So if we want to concatenate all the names, employee IDs, and work_ date of above table, then we can do it using the following command –

SQL >  SELECT CONCAT (id , name , work_date )  

->FROM employee_ tbl;    

CONCAT(id, name, work_date)

1Michal2009-02-15

2Zeena2003-03-24

2kachner2007-08-19

2warner2007-04-25

3joy2007-05-17

4atire2006-06-23

5delph2004-05-28

Example 2:

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 ||)  

Output:

id	last_name	first_name	first_name||last_name	salary	first_name||salary
1	bean	Mr.	Mr.bean	10000	Mr.10000
2	William	Sunita	Sunita William	50000	Sunita50000
3	tpoint	Java	Javatpoint	20000	Java20000
4	&example	tutorial	tutorial&example	90000	Tutorial90000

Note: In above example, we have used “||”, which is known as the Concatenation operator, and it is used to link two or more columns in select query. This operator is independent of the data type of column.
Here, we have linkined 2 columns i.e, first_name+last_name as well as first_name+salary.

We can use string literals in CONCAT operator.

Example 1: Using the 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 new ‘name’)

id	first_name	last_name	salary	new
1	Javatpoint	tpoint	20000	Java has salary 20000
2	tutorial	&example	30000	the tutorial has salary 30000
3	Shane	Watson	40000	Shane has salary 40000
4	Jennifer	louse	60000	Jennifer has salary 60000

Note: We have used salary as a character literal in the select statement. We can use the date literal and number literal according to our requirement in the table.

Example 2: Using character as well as the number literal

Syntax:

SELECT id, first_name, last_name, salary, first_name||100||'   

has id '||id AS "new" FROM myTable  

Output (Making  the output readable by concatenating a string  

with values) 

Output:

id	first_name	last_name	salary	new
1	Javatpoint	tpoint	20000	Java100 has id 1
2	tutorial	&example	30000	Tutorial100 has id 2
3	Shane	Watson	40000	Shane100 has id 3
4	Jennifer	louse	60000	Jennifer100 has id 4

In the above example, we have used the salary as a character literal as well as 100 as number authentic in our select statement.