How to concatenate more than 2 fields with SQL?

To concatenate more than 2 fields with SQL, you can use CONCAT() or CONCAT_WS() function. The syntax is as follows. Let us first see using CONCAT().

SELECT CONCAT(yourColumnName1,'/',yourColumnName2, '/',yourColumnName3, '/',......N) AS anyVariableName FROM yourTableName;

The syntax is as follows:

SELECT CONCAT_WS(‘/’,yourColumnName1,yourColumnName2,.....N) AS anyVariableName FROM yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table MoreThan2ColumnConcat
   -> (
   -> Id int,
   -> Name varchar(20),
   -> Age int,
   -> Marks int
   -> );
Query OK, 0 rows affected (2.59 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into MoreThan2ColumnConcat values(1,'John',24,89);
Query OK, 1 row affected (0.17 sec)
mysql> insert into MoreThan2ColumnConcat values(11,'Larry',25,90);
Query OK, 1 row affected (0.21 sec)
mysql> insert into MoreThan2ColumnConcat values(15,'Mike',26,79);
Query OK, 1 row affected (0.13 sec)
mysql> insert into MoreThan2ColumnConcat values(16,'Sam',21,99);
Query OK, 1 row affected (0.14 sec)

Now you can display all records from the table using select statement. The query is as follows:

mysql> select *from MoreThan2ColumnConcat;

The following is the output:

+------+-------+------+-------+
| Id   | Name  | Age  | Marks |
+------+-------+------+-------+
|    1 | John  |   24 |    89 |
|   11 | Larry |   25 |    90 |
|   15 | Mike  |   26 |    79 |
|   16 | Sam   |   21 |    99 |
+------+-------+------+-------+
4 rows in set (0.00 sec)

Here is the query to concatenate more than two fields with CONCAT().

mysql> select concat(Id,'/',Name, '/',Age, '/',Marks) as ConcatMoreFields from MoreThan2ColumnConcat;

The following is the output:

+------------------+
| ConcatMoreFields |
+------------------+
| 1/John/24/89     |
| 11/Larry/25/90   |
| 15/Mike/26/79    |
| 16/Sam/21/99     |
+------------------+
4 rows in set (0.00 sec)

Let us see the query to concatenate more than two fields using CONCAT_WS().

mysql> select concat_ws('/',Id,Name,Age,Marks) as ConcatMoreFields from MoreThan2ColumnConcat;

The following is the output:

+------------------+
| ConcatMoreFields |
+------------------+
| 1/John/24/89     |
| 11/Larry/25/90   |
| 15/Mike/26/79    |
| 16/Sam/21/99     |
+------------------+
4 rows in set (0.00 sec)

Chandu yadav

Published on 25-Feb-2019 10:18:46

Previous Page Print Page