Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select only columns with a certain property from an SQL database

Posted on 2009-05-08
3
Medium Priority
?
220 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
  • 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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

927 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