select field names contain similar words !

Hi experts,

I got a table contains field(column) names as follows:

term1   term2   term3....................   week1  week2   week3    week4.....................day1   day2   day3..................

I want to select all field names containing "term" or "week" or "day" separately according to my needs , and also I needed to know how many columns is retrieved for each choice !!
eg, I want to select all field names containiing "term" and there are three columns returned in my resultset !!

Many thanks !
Mike
mmccyAsked:
Who is Participating?
 
OmnibuzzCommented:
try this.

declare @input varchar(10)
set @input = 'term'

select column_name from information_Schema.columns where column_name like '%' + @input + '%'
and table_name = 'your_table_name'

and if you want the count
use

select count(column_name) from information_Schema.columns where column_name like '%' + @input + '%'
and table_name = 'your_table_name'

Hope this is what you want.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
-- Got this from somebody a while back.  This will work for you.  Just change
-- the @phrase, i.e. select @phrase = '%houston%'.

declare @phrase varchar(8000)
declare @OutFullRecords bit
declare @sql varchar(8000)
declare @tbl varchar(128)
declare @col varchar(128)
declare @id_present bit


declare @is_char_phrase bit
declare @min_len int
declare @loop_idx int
declare @loop_chr char(1)


set nocount on
select @OutFullRecords = 0
select @phrase = '%houston%'


select @loop_idx=1, @is_char_phrase=0, @min_len=0


while @loop_idx<=LEN(@phrase)
begin
  set @loop_chr=SUBSTRING(@phrase,@loop_idx,1)
  if @loop_chr not in ('%','_') set @min_len=@min_len+1
  if @is_char_phrase=0 and @loop_chr not in
('%','_','0','1','2','3','4','5','6','7','8','9','.')
       set @is_char_phrase=1
  set @loop_idx=@loop_idx+1
end

-- drop table #tbl_res
create table #tbl_res
                (TableName varchar(128) not NULL,
                 ColumnName varchar(128) not NULL,
                 Id int NULL,
                 ColumnValue varchar(7500) not NULL)


declare CRR cursor local fast_forward for
  select t.name, c.name, 1
  from sysobjects t, syscolumns c
  where t.type='U'
  and c.id=t.id
  and c.status&0x80=0 -- Not IDENTITY
  and exists (select * from syscolumns c2 where t.id=c2.id and
c2.status&0x80=0x80 and c2.xtype in (48,52,56))
  and (   (@is_char_phrase=1 and c.xtype in (175,239,99,231,35,167) and
c.length>=@min_len) -- char only
       or (@is_char_phrase=0 and c.xtype not in
(34,165,173,189,61,58,36))) -- char and numeric
  union select t.name, c.name, 0
  from sysobjects t, syscolumns c
  where t.type='U'
  and c.id=t.id
  and not exists (select * from syscolumns c2 where t.id=c2.id and
c2.status&0x80=0x80 and c2.xtype in (48,52,56))
  and (   (@is_char_phrase=1 and c.xtype in (175,239,99,231,35,167) and
c.length>=@min_len) -- char only
       or (@is_char_phrase=0 and c.xtype not in
(34,165,173,189,61,58,36))) -- char and numeric
  order by 1,2


open CRR
fetch CRR into @tbl, @col, @id_present
while @@FETCH_STATUS=0
begin
  if @OutFullRecords=0
  begin
    set @sql='insert into #tbl_res (TableName,ColumnName,Id,ColumnValue) '
            +'select '+char(39)+@tbl+char(39)+', '
                      +'['+@col+']'+', '
    if @id_present=1 set @sql=@sql+'IDENTITYCOL, '
                else set @sql=@sql+'NULL, '
    set @sql=@sql+'convert(varchar(7500),['+@col+']) '
                 +'from ['+@tbl+'] (nolock) '
                 +'where convert(varchar(8000),['+@col+']) like
'+char(39)+@phrase+char(39)
--     select @sql
  end
  if @OutFullRecords=1
  begin
    set @sql='if exists (select * from '+@tbl+' (nolock) '
                       +'where convert(varchar(8000),['+@col+']) like
'+char(39)+@phrase+char(39)+') '
            +'select '+char(39)+@tbl+char(39)+' TableName,
'+char(39)+@col+char(39)+' ColumnName, * '
            +'from '+@tbl+' (nolock) where convert(varchar(8000),'+@col+')
like '+char(39)+@phrase+char(39)
  end
  exec(@sql)
  fetch CRR into @tbl, @col, @id_present
end
close CRR
deallocate CRR


if @OutFullRecords=0
begin
  -- For the clients supporting new types:
  --exec('select * from #tbl_res order by 1,2,3')


  -- For the clients who are not supporting new types:
  exec('select TableName, ColumnName, Id, convert(varchar(255),ColumnValue)
ColumnValue from #tbl_res order by 1,2,3')
end


0
 
Scott PletcherSenior DBACommented:
USE yourDbName

DECLARE @searchFor NVARCHAR(50)
SET @searchFor = N'%term%'  --<<-- adjust as needed


DECLARE @columnNames VARCHAR(1000)
DECLARE @columnCount INT

SELECT @columnNames = ISNULL(@columnNames + ', ', '') + CAST(name AS VARCHAR(128))
FROM syscolumns WITH (NOLOCK)
WHERE name LIKE N'%term%'  --<<--

SET @columnCount = @@ROWCOUNT

SELECT @columnCount AS [Col Count],
    @columnNames AS [Col Names]
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.

All Courses

From novice to tech pro — start learning today.