• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Select only columns with a certain property from an SQL database

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
RTSol
Asked:
RTSol
  • 2
1 Solution
 
RiteshShahCommented:
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
 
RiteshShahCommented:
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
 
RTSolAuthor Commented:
Thanks a lot - works perfect. Great article by the way.
RTSol
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now