Is it possible to do "like ..." when selecting your column names in SQL?

Question

So I want to do a simple select like so:

select * from table

But this table has, for example, 100 columns. I'll only need the columns where "substring" is in the name, like say table has 3 columns: "firstPoints", "secondPoints", "thirdPoints", and then 97 columns without "Points" in the name, is there a way to do a select that only selects the columns that have the substring "Points" in it's name? Like:

select * like '%Points%' from table

Or something like that, so that, without explicitly passing the columns I want, I still get the select but only of those 3 columns that has "Points" in their name.

Of course it is possible. Same class as: https://stackoverflow.com/questions/413819/select-except. Depending on your RDBMS - dynamic SQL/ polymorphic table functions/trick with JSON/XML. — Lukasz Szozda, May 25 '18 at 08:46
First, tag your DBMS. Second, you'll have to use dynamic sql — George Menoutis, May 25 '18 at 08:47
No, it is not possible in most databases with a simple `select` statement. You can do this using dynamic SQL. — Gordon Linoff, May 25 '18 at 11:12

score 0 · Answer 1 · answered May 25 '18 at 12:16

The Answer is like this:

  SELECT TOP 1000 COLUMN_NAME
  FROM {databasename}.INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = '{tablename}' and COLUMN_NAME like '%Datum%'

This will return a column "COLUMN_NAME" with records of my column names on that table containing "Datum" for example, in my example on my DB it returns 6 column names.

Is it possible to do "like ..." when selecting your column names in SQL?

1 Answers1