Solved

SELECT "all except" columns

Posted on 2008-10-01
6
869 Views
Last Modified: 2012-05-05
Ok so here's the deal. I'm working with tables at work that were not well designed. These tables are converted flat files (from mainframe) so one table may have 50 or more columns of information (which may or may not be related). Lets say I want to pull some records from such a table but out of the 40 columns, I want all of them except 2 (those 2 being an SSN and/or a crypted password; something along those lines). Is there a quick way to say:

SELECT * (-SSN, -password) FROM [table]

instead of having to manually write out

SELECT col1, col2, col3... col37, col38  ?
0
Comment
Question by:MMDeveloper
  • 2
  • 2
  • 2
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 22613762
>Is there a quick way to say:
no.  at least not with sql itself.

however, you could use a sql to query for all the column names of the table/view... and just make the list of all the columns you want.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 22613764
yes, I'm afraid so.  There is no "all except" syntax in SQL
0
 
LVL 15

Author Comment

by:MMDeveloper
ID: 22613798
afterthought... lets say that the 2 columns I dont want are called "ssn" and "ssn2"

could I do

select *, '.' as ssn, '.' as ssn2

or woudl that give me the ambiguous column error deal (sorry I know I should have just tried it but I dont have a connection to the SQL server yet (they're working on it)
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22613808
>or would that give me the ambiguous column error deal
yes, that's what you would get.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22613824
some tools may help you build your query too.  Toad for MySQL should let you drag the columns you want from a browser into a query.
0
 
LVL 15

Author Closing Comment

by:MMDeveloper
ID: 31501946
I was afraid of this, thanks you two.
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Find results from sql within a time span 11 45
Connection to multiple databases 13 25
SQL Syntax: How to force case sensitive query? 2 44
Merge two rows in SQL 4 25
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question