Solved

Select only columns with a certain property from an SQL database

Posted on 2009-05-08
3
217 Views
Last Modified: 2012-05-06
Hi,

I have a table in an SQL database with a lot of columns. They have names with different prefixes like par_column1, par_column2, par_column3, fit_column4, fit_column5 and so on. Now I would like to issue a SELECT statement that selects only columns with a certain prefix, for instance 'par', like this:
SELECT "columns that start with 'par'" FROM Table1 WHERE Colum0 = 'criteria'

Can this be done - and if so - how?

Best regards
RTSol
0
Comment
Question by:RTSol
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24335113
it is possible, have a look at my article at

http://www.sqlhub.com/2009/05/dynamic-select-statement-with-sp-by.html

you can customize your logic
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24335125
have a look at below SP, and customize it for your own usage.


--SP which will accept tablename and character of the column name
--which needs to be eliminated from the list of results.
create proc DynamicSelect
@TableName varchar(20),
@FilterChar char(1)
AS
BEGIN
SET NOCOUNT ON
      DECLARE @Cols NVARCHAR(500)
      SET @Cols=''
      --gathering column list in @Cols variable by querying Information_Scehma.columns table
      SELECT @Cols=@Cols+ s.column_name + ', ' FROM
      (select COLUMN_NAME from  information_schema.columns where TABLE_NAME='TableA' and charindex(@FilterChar,COLUMN_NAME)>0) AS s
      SET @Cols=LEFT(@Cols,LEN(@Cols)-1)
      set @cols='SELECT ' + @Cols + ' From ' + @TableName
      EXECUTE sp_executeSQL @Cols
END
 
 
--check SP whether it actually works!!!!
Exec DynamicSelect 'Table1','par'

Open in new window

0
 

Author Closing Comment

by:RTSol
ID: 31579380
Thanks a lot - works perfect. Great article by the way.
RTSol
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 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