CODEWITHSUNDEEP
mysql
norbdum
norbdum

Reputation: 2491

MySQL: Select Column names containing a string

I am trying to get data from columns containing a particular string in their name in my case it it PLA. Though their is a way to select where the values are like as in MySQL query String contains. Is there a similar way for the column name?

SELECT COLUMN NAMES LIKE '%PLA%' FROM `TABLENAME` WHERE MONTH=? AND YEAR=?

Upvotes: 8

Views: 37884

Answers (2)

ddisisto
ddisisto

Reputation: 451

SELECT COLUMN NAMES is not valid in MySQL. There are similar things such as DESCRIBE [table]; or SHOW COLUMNS FROM [table];, but you can't put WHERE clauses on them. Much better is to use the INFORMATION_SCHEMA virtual DB e.g. -

SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS`
 WHERE `TABLE_SCHEMA`='[database name]' and `TABLE_NAME`='[table name]'
 AND   `COLUMN_NAME` LIKE '%PLA%'

See http://dev.mysql.com/doc/refman/5.6/en/columns-table.html for more information.

Upvotes: 20

Muhmmad Kuti
Muhmmad Kuti

Reputation: 105

Have you tried this?

SELECT (SELECT COLUMN NAMES FROM 'TABLENAME') AS CNAME WHERE CNAME LIKE '%PLA%'

Upvotes: -2

Related Questions