Select particular type of columns beginning with a certain letter and concatenate the names

For particular type of columns beginning with a certain letter, use LIKE. To concatenate the column names, use GROUP_CONCAT() as in the below syntax −

SELECT group_concat(COLUMN_NAME separator ' , ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = "yourTableName"
AND table_schema = "yourDatabaseName"
AND column_name LIKE "yourSpecificLetter%";

Let us first create a table −

mysql> create table DemoTable671(
   ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ClientName varchar(100),
   ClientAge int,
   ClientAddress varchar(200),
   ClientCountryName varchar(100)
);
Query OK, 0 rows affected (0.62 sec)

Following is the query to select particular type of columns beginning with a certain letter and fetch all the column names for concatenation −

mysql> SELECT group_concat(COLUMN_NAME separator ' , ')
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_name = "DemoTable671"
   AND table_schema = "web"
   AND column_name LIKE "Cl%";

This will produce the following output −

+-----------------------------------------------------------------------+
| group_concat(COLUMN_NAME separator ' , ')                             |
+-----------------------------------------------------------------------+
| ClientId , ClientName , ClientAge , ClientAddress , ClientCountryName |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

AmitDiwan

e

Updated on: 26-Aug-2019

61 Views

Kickstart Your Career

Get certified by completing the course

Get Started