We help IT Professionals succeed at work.

SELECT "all except" columns

MMDeveloper
MMDeveloper asked
on
907 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  ?
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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)
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>or would that give me the ambiguous column error deal
yes, that's what you would get.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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.

Author

Commented:
I was afraid of this, thanks you two.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.