MySQL select query

Question

Possible Duplicate:
MySQL wildcard in select …

SELECT icon_* FROM images WHERE 1

I have three fields, icon_small, icon_big, and icon_large. How do I get all three without manually specifying them?

score 3 · Accepted Answer · answered Jun 03 '11 at 07:58

3

As far as I know, you can't. You will have to manually specify them.

(See the duplicate)

answered Jun 03 '11 at 07:58

Pekka

431,103
135
960
1,075

score 1 · Answer 2 · edited Jul 24 '11 at 22:20

1

You have to specify them in your SELECT, but you can select a list of columns (which can then only be used in dynamic SQL) by doing:

select column_name from information_schema.columns 
                   where table_schema = database()
                         and table_name = 'mytesttable' 
                         and column_name like 'icon_%'

edited Jul 24 '11 at 22:20

Peter Mortensen

30,030
21
100
124

answered Jun 03 '11 at 08:03

davek

21,791
7
74
94

score 0 · Answer 3 · answered Jun 03 '11 at 08:17

set @qry = (select concat('select ',group_concat(column_name), ' from ' ,table_name) from
information_schema.columns
where table_schema = database()
and table_name = 'your_table_name'
and column_name like 'icon_%');

prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;

MySQL select query

3 Answers3