SQL - exclude a column from SELECT query

Question

I have 100 columns in a table, i want to list 99 columns except a particular one, how to exclude that columns name?

Answer 1

SELECT column_1, column_2, column_3, 
     /* ...the list of columns 4-97, not shown in this example... */,
     column_98, column_99
   FROM table

There is no easier way to do this (possibly by design): you need to specifically list each of the columns you want to retrieve.

Although it's a hassle to write, this is actually a Good Thing: using SELECT * in production code is discouraged (both for performance and maintainability reasons) - see e.g. this question.

Answer 2

There is no syntax such as * - col1 that allows you to do this. You need to explicitly list the specific ones that you want.

You could use a View if you commonly need this same set of columns.

Answer 3

You cannot.

Theoretically, you might involve dynamic sql here - some procedure will loop through all columns of your table (like, all_tabs_columns dictionary view in Oracle lists all columns of all tables), and form up you a query of select c1, c2 c3 ... c99 from tab, but I strongly discourage you of doing this. Will save you several lines of code, but make it hard to maintain. :)