Search code examples
sqldatabasesqlitesql-like

SQLite, LIKE column name

What query could I use in sqlite to get the names of columns beginning with (for example) "thing" in a DB
Such as if they were formatted like this:
"thing_column1"
"thing_column2"
"thing_data" etc.

Solution

  • You can use pragma_table_info() with the table's name:

    SELECT name 
FROM pragma_table_info('tablename')
WHERE name LIKE 'thing%'

    You can use this query:

    SELECT GROUP_CONCAT(name) AS columns 
FROM pragma_table_info('tablename')
WHERE name LIKE 'thing%'

    which returns only 1 column columns with a string value like 'thing_column1,thing_column2,thing_column3' and you can use it to construct a SELECT statement in your application.