Solved

Select only columns with a certain property from an SQL database

Posted on 2009-05-08
3
209 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 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

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
SQL Help - 12 60
Log Backup 2 21
Sql query to Stored Procedure 6 38
sql Total query 2 14
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
A short film showing how OnPage and Connectwise integration works.

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

20 Experts available now in Live!

Get 1:1 Help Now