4 Replies Latest reply: Feb 26, 2013 9:40 AM by 973995 RSS

    how to remove column name in select clause

    973995
      Hello Guys,

      I just want to remove a column name in select clause. Because, I don't want to write all column names. I hope I express myself.

      In other words, I want the following.
      Select   * - unwanted_column  from table;
      
      instead of this 
      
      Select col1, col2, col3, col4, ........ col 10000 from table;
        • 1. Re: how to remove column name in select clause
          Paul  Horth
          970992 wrote:
          Hello Guys,

          I just want to remove a column name in select clause. Because, I don't want to write all column names. I hope I express myself.

          In other words, I want the following.
          Select   * - unwanted_column  from table;
          
          instead of this 
          
          Select col1, col2, col3, col4, ........ col 10000 from table;
          As far as I know, it can't be done.

          Where is the select going to be used?
          • 2. Re: how to remove column name in select clause
            sb92075
            970992 wrote:
            Hello Guys,

            I just want to remove a column name in select clause.
            Then write the column names without including unwanted column.
            Because, I don't want to write all column names. I hope I express myself.

            In other words, I want the following.
            Select   * - unwanted_column  from table;
            
            instead of this 
            
            Select col1, col2, col3, col4, ........ col 10000 from table;
            Table with 1000 columns is likely NOT Normalized & needs to be fixed.
            • 3. Re: how to remove column name in select clause
              Frank Kulash
              Hi,

              Sorry, there's nothing in SQL that means "all columns *except* ...". As the others have said, the only way to get those results in SQL is to list all the columns you do want.

              Your front end may have some feature that allows you to hide a specific column. For example, in SQL*Plus, you can use <tt> COLUMN ... NOPRINT </tt> , like this:
              COLUMN      dname     NOPRINT
              
              
              SELECT       *
              FROM       scott.dept
              ORDER BY  dname
              ;
              Output:
              `   DEPTNO LOC
              ---------- -------------
                      10 NEW YORK
                      40 BOSTON
                      20 DALLAS
                      30 CHICAGO
              There is a column called dname in the scott.dept table; the query above actually uses it. But, because of the COLUMN command, SQL*Plus won't display that column.

              Edited by: Frank Kulash on Feb 26, 2013 10:10 AM
              Changed scott.dept example.
              • 4. Re: how to remove column name in select clause
                973995
                Thanks a lot Frank.