CODEWITHSUNDEEP
mysqlselectmatch
Josh
Josh

Reputation: 41

mysql - How can I select columns based on their name

I want to run a query that selects certain columns if their name contains a string. So say I have columns col_1, col_2, col_3, example_1, example_2 I want to run:

SELECT example_1, example_2 FROM `table`

but with a pattern match.. So:

SELECT LIKE %`example_`% FROM `table`

I can't seem to find how to do this anywhere

Upvotes: 4

Views: 4330

Answers (3)

Siva
Siva

Reputation: 8058

Im a bit late here,

Indeed its possible with MySQL Stored Procedures

DELIMITER //
create procedure custom_select()
    begin
    declare str_var TEXT;
    select GROUP_CONCAT(column_name) into str_var from information_schema.columns where table_name ="YOUR_TABLE_NAME" and  column_name like "example_%" and table_schema="YOUR_DATABASE";
    set @q = CONCAT('select ', str_var, ' from table_name ');
    prepare stm from @q;
    execute stm;
    end//

And Call them by

 delimiter ;
 call custom_select();

Upvotes: 3

dipendra
dipendra

Reputation: 235

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.

You can get the similar column name using following query:

  select * from information_schema.columns where table_name='table1' and column_name like 'a%'

Upvotes: 0

Charlie Martin
Charlie Martin

Reputation: 8406

You can't use wildcards like you can for the values of the column for the name of the column. The best you can do is use a server side language like php to formulate your query with variables as the column names. For example..

$query = 'select ' . $columnName1 . ', ' . $columnName2 . ' from myTable;';

Upvotes: 1

Related Questions