Solved

SELECT "all except" columns

Posted on 2008-10-01
6
867 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 142

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 73

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 142

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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Very interesting Access query problem. 13 70
sql query Help 12 52
join 2 views with 5 conditions 3 43
email about the whoisactive result 7 21
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

914 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now