Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Select only columns with a certain property from an SQL database

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

     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 …
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 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