SQL - CONCAT() Function



The SQL CONCAT() function accepts a one or more string values as parameters, concatenates/join all the given strings and returns the result.

When we display the result, the Concat service converts the Null values to an empty string. The operator is used to concatenate character strings and column strings. In the CONCAT function, We can use a literal. A literal is a number, character, or date that is contained in a SELECT statement.

Syntax

Following is the syntax of this SQL CONCAT() function −

CONCAT(str1,str2,... strN);
SELECT CONCAT(str1,str2,... strN);

This syntax uses an CONCAT function with SQL table column names −

SELECT Column1, Column2, ………… ColumnN, CONCAT(Column1, Column2, ………… ColumnN) AS CONCAT_Function FROM table name; 

Parameters

  • str − The CONCAT function takes at least two parameters and can accept a total of 254 parameters.

Return value

The SQL CONCAT() function returns the same string as the operator returns from the same expressions as the operands.

Example

Following is an example of the SQL CONCAT() function where we are passing only one argument to it. So, here there is no concatenation −

SELECT CONCAT('Revathi');

Output

Following is the output of the above query −

+-------------------+
| CONCAT('Revathi') |
+-------------------+
| Revathi           |
+-------------------+

Example

Following is an example of the SQL CONCAT() function where we are passing two arguments to concatenate −

SELECT CONCAT('Tutorials', 'point');

Output

The above SQL query produces the following output −

+------------------------------+
| CONCAT('Tutorials', 'point') |
+------------------------------+
| Tutorialspoint               |
+------------------------------+

Example

Following is an example of the SQL CONCAT() function where we are passing more than two arguments to concatenate −

SELECT CONCAT('Structured', 'Query', 'Language') AS CONCAT_Function;

Output

On executing the above query, it will produce the following output −

+-------------------------+
| CONCAT_Function         |
+-------------------------+
| StructuredQueryLanguage |
+-------------------------+

Example

Here, Let’s us pass binary strings as parameters to the CONCAT() function, it returns a binary string.

SELECT CONCAT('10001011', '10000000') AS CONCAT_function;

Output

On executing the above query, it will produce the following output −

+------------------+
| CONCAT_function  |
+------------------+
| 1000101110000000 |
+------------------+

Example

In the same way if you pass nonbinary values as arguments to the CONCAT() function, it returns a nonbinary string.

SELECT CONCAT('Good', 'Morning') AS CONCAT_Function;

Output

On executing the above query, it will produce the following output −

+-----------------+
| CONCAT_Function |
+-----------------+
| GoodMorning     |
+-----------------+

Here we can also pass both binary and nonbinary values as arguments to this CONCAT() function.

SELECT CONCAT('Rollno', '110101') AS CONCAT_Function;

Output

On executing the above query, it will produce the following output −

+-----------------+
| CONCAT_Function |
+-----------------+
| Rollno110101    |
+-----------------+

Example

If you pass a numeric value as an argument to the CONCAT() function, it converts the numeric values into its equivalent nonbinary string form −

SELECT CONCAT('62001', '12345', '14203') AS CONCAT_Function;

Output

On executing the above query, it will produce the following output −

+-----------------+
| CONCAT_Function |
+-----------------+
| 620011234514203 |
+-----------------+

Example

Following is an example where we are passing an empty string as an argument to the CONCAT() function −

SELECT CONCAT(' ');

Output

On executing the above query, it will produce the following output −

+-------------+
| CONCAT(' ') |
+-------------+
|             |
+-------------+

Example

If we are passing more than one argument to tha CONCAT() function and anyone of the argument is null, then the result will also be null −

SELECT CONCAT('ALL', 'IS', 'WELL', NULL) AS CONCAT_Function;

Output

On executing the above query, it will produce the following output −

+-----------------+
| CONCAT_Function |
+-----------------+
| NULL            |
+-----------------+

Example

You can pass the table column as an argument to the CONCAT() function to convert the character or string into a concatenate . Assume we have created a table with the name Customers using the CREATE statement as follows −

create table CUSTOMERS(
   ID INT NOT NULL, 
   NAME VARCHAR(15) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR(25), 
   SALARY DECIMAL(10, 4), PRIMARY KEY(ID)
);

Now let's insert seven records into the customers table using the INSERT statement as follows:−

insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(6, 'Komal', 22, 'MP', 4500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(7, 'Muffy', 24, 'Indore', 10000.00);

The following SELECT query uses the CONCAT function with the ID, NAME column of the CUSTOMERS table −

SELECT ID, NAME, CONCAT(ID, NAME) AS CONCAT_Function FROM CUSTOMERS;

Output

After executing the above statement, it produces the following output −

+----+----------+-----------------+
| ID | NAME     | CONCAT_Function |
+----+----------+-----------------+
|  1 | Ramesh   | 1Ramesh         |
|  2 | Khilan   | 2Khilan         |
|  3 | kaushik  | 3kaushik        |
|  4 | Chaitali | 4Chaitali       |
|  5 | Hardik   | 5Hardik         |
|  6 | Komal    | 6Komal          |
|  7 | Muffy    | 7Muffy          |
+----+----------+-----------------+

The following SELECT query uses the CONCAT function with all the column of the CUSTOMERS table, the columns contains ID, NAME, AGE, ADDRESS and SALARY.

SELECT ID, NAME, AGE, ADDRESS, SALARY, CONCAT(ID, NAME, AGE, ADDRESS, SALARY) AS CONCAT_Function FROM CUSTOMERS;

Output

After executing the above statement, it produces the following output −

+----+----------+-----+-----------+------------+-----------------------------+
| ID | NAME     | AGE | ADDRESS   | SALARY     | CONCAT_Function             |
+----+----------+-----+-----------+------------+-----------------------------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.0000 | 1Ramesh32Ahmedabad2000.0000 |
|  2 | Khilan   |  25 | Delhi     |  1500.0000 | 2Khilan25Delhi1500.0000     |
|  3 | kaushik  |  23 | Kota      |  2000.0000 | 3kaushik23Kota2000.0000     |
|  4 | Chaitali |  25 | Mumbai    |  6500.0000 | 4Chaitali25Mumbai6500.0000  |
|  5 | Hardik   |  27 | Bhopal    |  8500.0000 | 5Hardik27Bhopal8500.0000    |
|  6 | Komal    |  22 | MP        |  4500.0000 | 6Komal22MP4500.0000         |
|  7 | Muffy    |  24 | Indore    | 10000.0000 | 7Muffy24Indore10000.0000    |
+----+----------+-----+-----------+------------+-----------------------------+
sql-string-functions.htm
Advertisements