how can I query for conditions on column_names

[ufaruq1]

i have my database with the columns as follows:

keyword, part1_d1, part1_d2 ........ part1_d25, part2_d26, ......part2_d34

FYI: d1 through d34 are documents..

how can I give a query to obtain columns with column_name like '%part1%'; as below

keyword, part1_d1, part1_d2, ........ part1_d25

I tried the query:

select (Select COLUMN_NAME From INFORMATION_SCH EMA.COLUMNS where COLumn_NAME like '%part1%') , keyword from sample

But it dint work...

Please let me know what to do?

[deepuv04]

hi,
I think you are trying to get the column names dynamically. if that is the requirement... you need to build the query dynamically and execute the query string.

Try the following

DECLARE @STR VARCHAR(MAX)
SET @Str = 'SELECT '

SELECT	@Str = @Str + Column_Name + ',' FROM Information_Schema.Columns
WHERE	TABLE_Name = 'Table_Name' AND 
		Column_Name like '%cOLUMN_Name%'


SELECT @Str = SUBSTRING(@Str,0,len(@Str)) + ' FROM Table_Name' 

SELECT @Str

EXEC (@Str)