mysql: Selecting multiple columns using LIKE

Question

I have many columns which are of the form: rp_id_1 rp_id_2 and so on. I want to get data for these specific columns. On the forum I see below type of query being suggested:

"SELECT column_name
FROM information_schema.columns
WHERE table_name='loc_tbl'
AND column_name LIKE 'rp%'"

However this only returns the column names and not the data. Output:

+-------------+
| column_name |
+-------------+
| rp_id_1     |
| rp_id_2     |
| rp_id_3     |

What am I missing? How do I get the data for all these columns instead? Thanks.

Tried that. It returns data from the information_schema.columns table instead. — Nikhil Utane
– Nikhil Utane, Commented Oct 26, 2018 at 6:53

Tim Biegeleisen · Accepted Answer · 2018-10-26 07:10:18Z

2

We can do this using dynamic MySQL. From the MySQL command line:

SELECT @cols := GROUP_CONCAT(column_name) FROM information_schema.columns
    WHERE table_name = 'loc_tbl' AND column_name LIKE 'rp%';
SET @query = CONCAT("SELECT ", @cols, " FROM lob_tbl");
PREPARE stmt FROM @query;
EXECUTE stmt;

answered Oct 26, 2018 at 7:10

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql: Selecting multiple columns using LIKE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related