Solved

Select only columns with a certain property from an SQL database

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

14 Experts available now in Live!

Get 1:1 Help Now