Can I perform a query that returns data only for columns that match a wildcard?

Question

I have a table MyTable with many columns, but the names of those columns have a pattern, like:

`BLOCK_A_PARAM_1` INT,
`BLOCK_A_PARAM_2` INT,
`BLOCK_A_PARAM_3` INT,
`BLOCK_A_PARAM_4` INT,
--- ...
`BLOCK_A_PARAM_25` INT,
`BLOCK_A_PARAM_26` INT,
`BLOCK_B_PARAM_1` INT,
`BLOCK_B_PARAM_2` INT,
`BLOCK_B_PARAM_2` INT,
--- ...

This data is not spread across multiple tables for a handful of reasons. My table doesn't suffer from a lack of normalisation per se, though this task would be easier if each block lived in a different table. Regardless, here I am.

If I wanted to construct a query to return data only for columns whose names match BLOCK_A_%, how could I do it?

This is only for utility during development, and would never be part of my application's logic.

---

I already tried:

SELECT (SHOW COLUMNS FROM `MyTable`
        WHERE `Field` LIKE "BLOCK_A_%") FROM `MyTable`;

but the resulting error indicates that SHOW COLUMNS as a subquery is invalid syntax.

---

This second attempt:

SELECT * FROM (SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS`
               WHERE `TABLE_NAME` = "MyTable"
               AND `COLUMN_NAME` LIKE "BLOCK_A_%") `_`;

simply lists the table schema details (i.e. the outer query is totally useless).

---

And, finally:

SELECT (SELECT * FROM `INFORMATION_SCHEMA`.`COLUMNS`
        WHERE `TABLE_NAME` = "MyTable"
        AND `COLUMN_NAME` LIKE "BLOCK_A_%")
FROM `MyTable`;

but this results in the following error:

Operand should contain 1 column(s)

---

So, is there a way to do this? In MySQL, please; don't give me any PHP/ASP.NET/etc!

Answer 1

Using prepared statements, you can build the query dynamically from INFORMATION_SCHEMA, then execute it:

SET @QUERY=CONCAT_WS(" ",
   "SELECT",
    (SELECT GROUP_CONCAT(`COLUMN_NAME`) FROM `INFORMATION_SCHEMA`.`COLUMNS`
     WHERE `TABLE_NAME` = "MyTable" AND `COLUMN_NAME` LIKE "BLOCK_A_%"),
  "FROM `MyTable`");
PREPARE myQuery FROM @QUERY;
EXECUTE myQuery;