469,277 Members | 1,964 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,277 developers. It's quick & easy.

how can I query for conditions on column_names

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_SCHEMA.COLUMNS where COLumn_NAME like '%part1%') , keyword from sample

But it dint work...

Please let me know what to do?
Aug 13 '10 #1
1 972
deepuv04
227 Expert 100+
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


Expand|Select|Wrap|Line Numbers
  1. DECLARE @STR VARCHAR(MAX)
  2. SET @Str = 'SELECT '
  3.  
  4. SELECT    @Str = @Str + Column_Name + ',' FROM Information_Schema.Columns
  5. WHERE    TABLE_Name = 'Table_Name' AND 
  6.         Column_Name like '%cOLUMN_Name%'
  7.  
  8.  
  9. SELECT @Str = SUBSTRING(@Str,0,len(@Str)) + ' FROM Table_Name' 
  10.  
  11. SELECT @Str
  12.  
  13. EXEC (@Str)
Aug 13 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by ben | last post: by
1 post views Thread by Ryan Govostes | last post: by
7 posts views Thread by Jim | last post: by
2 posts views Thread by Justin Koivisto | last post: by
4 posts views Thread by ime | last post: by
2 posts views Thread by Jeff Gardner | last post: by
5 posts views Thread by Samik2003 | last post: by
1 post views Thread by sliverdigger | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.