Get content of columns starting with substring

Question

I have a table with the following structure:

| ID | abc1   | abc2   | abc3   | def1   | def2   | def3   |
|----|--------|--------|--------|--------|--------|--------|
| 1  | Text 1 | Text 2 | Text 3 | Text A | Text B | Text C |
| 2  | Text 4 | Text 5 | Text 6 | Text D | Text E | Text F |

I now want to get the contents of all columns starting with abc.

With

select column_name from information_schema.columns 
where table_name='tab1'
and (column_name like 'abc%')

I get the required columns:

abc1
abc2
abc3

How can I get their content as well? So the desired output would be:

| abc1   | abc2   | abc3   |
|--------|--------|--------|
| Text 1 | Text 2 | Text 3 |
| Text 4 | Text 5 | Text 6 |

My approach was to put that query in another query, like that:

select (select column_name from information_schema.columns 
where table_name='tab1'
and (column_name like 'abc%'))
from tab1

But I get this error:

Subquery returns more than 1 row

TimoSolo's answer seems to give a good example: http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure As you could pass in your table name and cols from your inner query. Note you'd likely have to use Group_concat(column_name SEPARATOR ', ')` to combine the fields — xQbert, Feb 02 '17 at 14:27

Vamsi Prabhala · Answer 1 · 2017-02-02T15:25:07.653

1

As mentioned in the comments, you need a dynamic SQL to get the values from the selected columns.

--get the column names in a comma separated format
SELECT GROUP_CONCAT(COLUMN_NAME)
INTO @COLS --variable that holds the columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME =  'tab1'
AND COLUMN_NAME LIKE 'abc%';

SET @TBL= 'tab1';

SET @S= CONCAT('select ',@COLS, ' from ', @TBL);

PREPARE STMT FROM @S;

EXECUTE STMT;

DEALLOCATE PREPARE STMT;

Sample Demo

edited Feb 02 '17 at 15:25

answered Feb 02 '17 at 14:57

Vamsi Prabhala

48,685
4
36
58

I just get an empty string. – Evgenij Reznik Feb 02 '17 at 15:18
I can execute your query without any errors, but I just get an empty result set. – Evgenij Reznik Feb 02 '17 at 15:23
I created the same table and populated it with the same data, just like in your example. But still I get the an empty result set, when testing in my environment (PHPMyAdmin). – Evgenij Reznik Feb 02 '17 at 15:48
However, when I remove the last line `DEALLOCATE PREPARE STMT;`, I get the information, that there are the expected 2 entries in my result set, but without the actual content. – Evgenij Reznik Feb 02 '17 at 16:04

Get content of columns starting with substring

1 Answers1