1

I have a SQLite table with columns like these: id, name_1, name_2, name_nick, phone, email

and I am looking for a way to search in all columns which begins with name_.

Like LIKE but for the column names.

I find a lot for MySQL etc, but nothing for SQLite.

These queries do not work as I want:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'


SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'Foods'
            AND table_schema = 'YourDB'
            AND column_name LIKE 'Vegetable%'

I get this:

ERROR: no such table: information_schema.columns

I have seen this question, which is how to obtain column names using SQL. However I think I would need a scripting language to run the query to get column names, filter them in some fashion, and then construct a new query using the filtered set of names. Instead I would like to try to do this all in SQL, if it is possible.

halfer
  • 19,824
  • 17
  • 99
  • 186
kolja
  • 505
  • 7
  • 24
  • Take a look at https://stackoverflow.com/questions/1054984/how-can-i-get-column-names-from-a-table-in-sql-server – Richard Sep 13 '20 at 10:19
  • This might be useful to get the column names: https://stackoverflow.com/questions/947215/how-to-get-a-list-of-column-names-on-sqlite3-database – halfer Sep 13 '20 at 10:19
  • @Richard: that link appears to be for SQL Server. – halfer Sep 13 '20 at 10:20
  • Does this answer your question? [SQL Column Name wildcard](https://stackoverflow.com/questions/49496911/sql-column-name-wildcard) – mayersdesign Sep 13 '20 at 10:33
  • The links from Richard and mayersdesign are solutions not working for SQLite, the link from @halfer is the way i want to avoid. – kolja Sep 13 '20 at 15:10
  • 1
    Please update your question to add the link I have offered, and then expand on why you want to avoid that (something like "I have seen but I don't want to use this technique because..."). For example, you would need a programming language to first obtain the column names, filter them by prefix, then build a query. You might want to do your task entirely in SQL - and if so please state that in the question itself. Of course it might not be possible. – halfer Sep 13 '20 at 15:43
  • (This question is liable to close. Please make the edits I have requested, and then we can see if the question can be reopened). – halfer Sep 13 '20 at 15:44
  • 1
    I see your edit, but I still don't think you are sufficiently explaining why getting column names in a programming language and creating the column list outside of SQL is a problem. – halfer Sep 13 '20 at 22:40
  • @halfer Everything that has to do with databases is quite new to me. So I would like to know what works and what doesn't. I can still make the detour via PHP if there is no suitable query in SQLite. But this is pretty close to my idea: https://stackoverflow.com/questions/1398720/how-to-use-like-with-column-name/60814644#60814644 – kolja Sep 14 '20 at 02:21
  • That doesn't answer my question `:=)`. However, I have made a reasonable guess at what your real question is - which is that you want to be able to do the task you have described entirely in SQL, using the SQLite engine. I have edited your question based on my assumptions. Please re-edit or comment if I have got something incorrect. – halfer Sep 14 '20 at 19:33
  • On the basis of my assumptions, I will see if I can get the question reopened. – halfer Sep 14 '20 at 19:44
  • Does this answer your question? [How can I get the list of a columns in a table for a SQLite database?](https://stackoverflow.com/questions/604939/how-can-i-get-the-list-of-a-columns-in-a-table-for-a-sqlite-database) – derpirscher Sep 14 '20 at 21:06

0 Answers0