1

I have a table like that :

id exemple_ABC tr_ABC exemple_BCD tr_BCD ....

I want to SELECT all of the column beginning by "exemple_" I try :

SELECT exemple_* FROM myTable
or
SELECT exemple_% FROM myTable

But all of this doesn't work...

kmas
  • 6,401
  • 13
  • 40
  • 62
user3608663
  • 115
  • 2
  • 16
  • 1
    you can build your query dynamically using the `columns` table of the `information_schema`. And no: you can't use wild cards for column names in a regular sql statement. – VMai Jun 19 '14 at 12:31
  • To add to VMai's comment, here's the [manual about the columns table](http://dev.mysql.com/doc/refman/5.1/en/columns-table.html) and a [sample use of that table](http://stackoverflow.com/a/23522270/2186023) – DrCopyPaste Jun 19 '14 at 12:34
  • Search for `[dynamic-sql]` and you'll find examples of writing queries that use `information_schema`. – Barmar Jun 19 '14 at 12:34
  • I try answer from Nirav Prajapati. I think that this will work, now I put the requete in my python script – user3608663 Jun 19 '14 at 12:47
  • @user3608663, try my solution... ;) With the one from Nirav Prajapati, you only get the column name. – kmas Jun 19 '14 at 13:06
  • I try your solution but I don't understand all off the code And I don't know how to do PREPARE and EXECUTE in python. – user3608663 Jun 19 '14 at 13:33

3 Answers3

0

You cannot with standard SQL. Column names are not treated like data in SQL.

If you use a SQL engine that has, say, meta-data tables storing column names, types, etc. you may select on that table instead.

try this one

select * from information_schema.columns 
where table_name='table1' and column_name like 'exemple_%'
Nirav Prajapati
  • 2,987
  • 27
  • 32
  • This works I do in too times : First I take the column Name from information_schema and second I select the value from myTable in the columnName. 1) colonne=select * from information_schema... 2) select colonne from myTable – user3608663 Jun 19 '14 at 13:35
0

You have to create a dynamic query thanks to the information_schema table.

SELECT
  CONCAT(
    'SELECT ',
    SUBSTRING_INDEX(
      GROUP_CONCAT(CONCAT('`', column_name, '`') ORDER BY column_name),
      ',',
      2),
    ' FROM your_table'
  )
FROM
  information_schema.columns 
WHERE
  table_schema=DATABASE() 
  AND table_name ='your_table'
  AND column_name LIKE 'your_prefix_%'
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

Have a look here (where I have found this query I have changed a little bit): select first N columns of MySQL table

Community
  • 1
  • 1
kmas
  • 6,401
  • 13
  • 40
  • 62
-1

It's a pretty strange situation not knowing the exact database design, but basically your only option is to wildcard ALL columns and choosing which you want on the client side, i.e:

SELECT * FROM myTable
Mattias Åslund
  • 3,877
  • 2
  • 18
  • 17
  • I know the database design but I have hundreds of column. I ask the database in python, maybe there is a way with this language – user3608663 Jun 19 '14 at 12:40