How do I select multiple columns having similar names

Question

I have a table STMT_TABLE and it has columns- ID, NAME, COL1, COL2... COL99. Now, I wish to select all the data in columns COL1 to COL99. Is there any replacement to (SELECT COL1, COL2... COL99 FROM tablename)?

Answer 1

You can use some kind of shell-scripting to solve this.

Here is one with powershell:

write-host -nonewline "select "
for ($i = 1; $i -le 98; $i++) {
    write-host -nonewline "COL$i, ";
}
write-host -nonewline "COL99";
write-host " from stmt_table where [...]"

I also use excel quite a bit for these kind of things, just create one column with the content "COL", and use the little square-thingy to copy it downwards. Then in the next column put 1 in the first row, 2 in the second, then 3, and use the little squarethingy to count. Column after this you put a comma and use the square-thingy. Then copy the whole thing to a text-editor, and use search-replace to remove and unwanted whitespace.

Answer 2

If you don't want to specify all the 100 columns you can:

select column_name || ',' 
  from user_tab_columns 
 where table_name = 'TABLE_NAME_IN_UPPER_CASE'
 order by column_id

Then copy the output and past it into your query (Don't forget to remove the last comma):

select <PASTE_HERE_PREVIOUS_OUTPUT> 
  from <YOUR_TABLE_NAME>

But you can't use it in automatic mode, most of work you do with your own hands.