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

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.

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz