?
Solved

select field names contain similar words !

Posted on 2006-05-12
3
Medium Priority
?
318 Views
Last Modified: 2011-10-03
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
0
Comment
Question by:mmccy
3 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16668533
-- 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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16668917
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
 
LVL 2

Accepted Solution

by:
Omnibuzz earned 2000 total points
ID: 16668943
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

840 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