Wildcards in column name for MySQL Wildcards in column name for MySQL sql sql

Wildcards in column name for MySQL

No, SQL doesn't provide you with any syntax to do such a select.

What you can do is ask MySQL for a list of column names first, then generate the SQL query from that information.

SELECT column_nameFROM information_schema.columnsWHERE table_name = 'your_table'    AND column_name LIKE 'word%'

let's you select the column names. Then you can do, in Python:

"SELECT * FROM your_table WHERE " + ' '.join(['%s = 1' % name for name in columns])

Instead of using string concatenation, I would recommend using SQLAlchemy instead to do the SQL generating for you.

However, if all you are doing is limit the number of columns there is no need to do a dynamic query like this at all. The hard work for the database is selecting the rows; it makes little difference to send you 5 columns out of 10, or all 10.

In that case just use a "SELECT * FROM ..." and use Python to pick out the columns from the result set.

No, you cannot dynamically produce the list of columns to be selected. It will have to be hardcoded in your final query.

Your current query would produce a result set with one column and the value of that column would be the string "word%" in all rows that satisfy the condition.

You can generate the list of column names first by using

SHOW COLUMNS IN tblname LIKE "word%"

Then loop through the cursor and generate SQL statement uses all the columns from the query above.

"SELECT {0} FROM searchterms WHERE onstate = 1".format(', '.join(columns))