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)
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 ;
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)
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
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’)
|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
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
|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.