2

Is there any way to select columns with wild cards.

like

to select columns with names having type could be 'SELECT %type% from table_name' ?

Mark
  • 1,381
  • 5
  • 18
  • 21

3 Answers3

3

Not really. You can use the * column wildcard to select all columns. If you're joining multiple tables, you can select all columns from specific table by prefixing * with the table name or alias:

SELECT a.id, a.title, b.*
  FROM articles AS a
    JOIN blurbs AS b ON a.id = b.article

However, you shouldn't use * unless you're writing a DB administration program.

Alternatively, you can build a statement within SQL or another language by fetching table metadata to get the column names. Using just MySQL, you can query the COLUMNS table in the INFORMATION_SCHEMA database to get the column names and use GROUP_CONCAT to build the column list for the statement.

SELECT CONCAT(
      'SELECT ',
      GROUP_CONCAT(COLUMN_NAME SEPARATOR ', '),
      ' FROM ', :db, '.', :table,
      ' WHERE ...'
      )
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=:db AND TABLE_NAME=:table

Replace ":db", ":table" and "..." with the appropriate values. You can even turn it into a prepared statement so you can use it for any table. From there, PREPARE and EXECUTE the constructed statement.

If you're not limited to SQL for programming, it should be less messy. The DB driver for your language of choice likely offers methods to get metadata. The actual implementation would be similar to the pure SQL approach (get column names, assemble statement, prepare, execute), but shouldn't be so ugly, as you'd be using an algorithmic, rather than declarative, language.

I would be very interested in seeing the situation that this is actually required..

Community
  • 1
  • 1
gnur
  • 4,671
  • 2
  • 20
  • 33
  • if you implement a class hierarchy as a normalized set of tables, you'll have several tables that are join-d to the base class table. if you know the type of the instance you want to retrieve, you know the table you want to read from. hence, the need for parametric table names. If I'm not mistaken, hibernate does a union query over all tables involved in these cases and handles the results accordingly. – Dan Apr 07 '11 at 13:57
  • SHOW COLUMNS FROM table_name where field like '%type%' - This could be helpful if used in subquery but its showing extra columns type, null, key, default, extra etc ... I only want column names better comma separated ... searching for solution ... – Mark Apr 07 '11 at 14:03
  • 1. Get required columns names using query 2. use result of point 1 and use in select query ... – Mark Apr 07 '11 at 14:05
1

You can find all fields that contains type within the name using the information_schema and then using prepared statement.

set @str = (concat('select ',(select concat(group_concat(column_name),' from ',table_name)
from information_schema.columns
where table_schema = 'your_db_name' and table_name = 'your_table_name' and column_name like '%type%')));
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98
0

Certainly possible if you are using a front-end language. If php just use

 $fieldlist= "cola, colb ";
 $tablename="tabl";
"select $fieldlist from $table"

My intuition is telling you are doing something simple using php-mysql but I may be wrong.

Mr. Zen
  • 704
  • 3
  • 7
  • 17