How to quote values using MySQL group_concat?

MySQLMySQLi Database

You can quote values using concat() and grop_concat() function from MySQL. The syntax is as follows −

SELECT GROUP_CONCAT(CONCAT(' '' ', yourColumnName, ' '' ' )) 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 Group_ConcatDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,  
   -> Value int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (1.56 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into Group_ConcatDemo(Value) values(100);
Query OK, 1 row affected (0.24 sec)

mysql> insert into Group_ConcatDemo(Value) values(120);
Query OK, 1 row affected (0.22 sec)

mysql> insert into Group_ConcatDemo(Value) values(234);
Query OK, 1 row affected (0.11 sec)

mysql> insert into Group_ConcatDemo(Value) values(2345);
Query OK, 1 row affected (0.24 sec)

mysql> insert into Group_ConcatDemo(Value) values(5678);
Query OK, 1 row affected (0.14 sec)

mysql> insert into Group_ConcatDemo(Value) values(86879);
Query OK, 1 row affected (0.16 sec)

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

mysql> select *from Group_ConcatDemo;

The following is the output −

+----+-------+
| Id | Value |
+----+-------+
|  1 | 100   |
|  2 | 120   |
|  3 | 234   |
|  4 | 2345  |
|  5 | 5678  |
|  6 | 86879 |
+----+-------+
6 rows in set (0.00 sec)

Here is the query to quote values using group_concat() −

mysql> select GROUP_CONCAT(CONCAT('''', Value, '''' )) as SingleQuote from Group_ConcatDemo;

The following is the output −

+-----------------------------------------+
| SingleQuote                             |
+-----------------------------------------+
| '100','120','234','2345','5678','86879' |
+-----------------------------------------+
1 row in set (0.09 sec)
raja
Published on 01-Feb-2019 06:05:08
Advertisements