MySQL concat_ws() method usage

To get the output MySQL query result in CSV format, use concat_ws(). The syntax is as follows −

SELECT CONCAT_WS(‘,’,yourColumnName1,yourColumnName2,yourColumnName3,....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 CSVFormatOutputs
   -> (
   -> StudentId int not null auto_increment,
   -> StudentName varchar(20),
   -> StudentAge int,
   -> PRIMARY KEY(StudentId)
   -> );
Query OK, 0 rows affected (1.15 sec)

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

mysql> insert into CSVFormatOutputs(StudentName,StudentAge) values('Mike',23);
Query OK, 1 row affected (0.26 sec)

mysql> insert into CSVFormatOutputs(StudentName,StudentAge) values('John',26);
Query OK, 1 row affected (0.19 sec)

mysql> insert into CSVFormatOutputs(StudentName,StudentAge) values('Sam',19);
Query OK, 1 row affected (0.20 sec)

mysql> insert into CSVFormatOutputs(StudentName,StudentAge) values('Carol',27);
Query OK, 1 row affected (0.59 sec)

mysql> insert into CSVFormatOutputs(StudentName,StudentAge) values('Bob',24);
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from CSVFormatOutputs;

The following is the output −

+-----------+-------------+------------+
| StudentId | StudentName | StudentAge |
+-----------+-------------+------------+
|         1 | Mike        |         23 |
|         2 | John        |         26 |
|         3 | Sam         |         19 |
|         4 | Carol       |         27 |
|         5 | Bob         |         24 |
+-----------+-------------+------------+
5 rows in set (0.00 sec)

Here is the MySQL query to get the output in CSV (Comma Separated Value) format to the screen using concat_ws() −

mysql> select concat_ws(',',StudentId,StudentName,StudentAge) as CSVFormat from CSVFormatOutputs;

The following is the output displaying the CSV format records −

+------------+
| CSVFormat  |
+------------+
| 1,Mike,23  |
| 2,John,26  |
| 3,Sam,19   |
| 4,Carol,27 |
| 5,Bob,24   |
+------------+
5 rows in set (0.00 sec)

George John

e

Updated on: 30-Jul-2019

84 Views

Kickstart Your Career

Get certified by completing the course

Get Started