[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 880
  • Last Modified:

SELECT "all except" columns

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
MMDeveloper
Asked:
MMDeveloper
  • 2
  • 2
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
sdstuberCommented:
yes, I'm afraid so.  There is no "all except" syntax in SQL
0
 
MMDeveloperAuthor 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)
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>or would that give me the ambiguous column error deal
yes, that's what you would get.
0
 
sdstuberCommented:
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
 
MMDeveloperAuthor Commented:
I was afraid of this, thanks you two.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now