Hi

Situation:

A table contain 10 columns, say a1, a2, ...

I want to select records based on the given value, say 'test'.  The
issue is I don't know the value 'test' is in which column.  It
could be in the column a1 or a2 or so on.

How I can get those records?

Thanks in advance

Hi
create table #test
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10),
col5 varchar(10),
)

insert into #test values ('test','g','h','o','h')
insert into #test values ('hh','yy','test','kkk','kk')
insert into #test values ('rrr','ffff','ddd','dddddd','888')

declare @p varchar(10)
set @p='%test%'

select * from #test
where (col1 like @p or col2  like @p or
     col3 like @p or  col4 like @p or col2 like @p)





Show quoteHide quote

Hi Uri Dimant,

Thanks for you reponse. But ...

My table contain more then 200 columns also it will grow.  So it is not
possible to mention the column names in the query.  Is there any way to
getting the records without methioning column names ??? (the query
should be generic)

Thanks

Overcomplicated queries are the natural consequence of poor design. Unless
you rethink this "horizontal expansion strategy" you will have difficulties
using it.


ML

---
http://milambda.blogspot.com/

How about something like this.
Regards
Shahriar



Create table atable
     (c1 varchar(20),
      c2 varchar(20),
      fname varchar(20),
      lname varchar(20),
      age int,
      car varchar(10))
go

insert into atable values ('test','2','3','4',22,'6')
insert into atable values ('1','test','3','4',22,'6')
insert into atable values ('1','2','test','4',22,'6')
insert into atable values ('1','2','3','4',22,'6')
insert into atable values ('1','2','3','4',22,'test')
insert into atable values ('test','test','test','test',22,'test')
go


Declare @select varchar(8000)
Declare @where varchar(8000)
Declare @quote char(1)
Declare @searchString varchar(50)
set @searchstring = 'test'
set @quote=''''

declare @tempColumn varchar(255)
DECLARE my_cursor CURSOR FOR
-- include only columns of type varchar
SELECT Column_name as c1 FROM information_Schema.COlumns where
table_name='atable' and data_type = 'varchar'
OPEN my_cursor
FETCH NEXT FROM my_cursor into @tempColumn
set @where = ''
WHILE @@FETCH_STATUS = 0
BEGIN
   if len(@where) > 0
      Set @where = @where + ' or '
   set @where = @where + @tempColumn+ '='+@quote + @searchstring + @quote
   FETCH NEXT FROM my_cursor into @tempColumn
END
CLOSE my_Cursor
DEALLOCATE my_cursor
if len(@where)> 0
  begin
Set @select = 'Select * from atable where ' + @where
exec (@Select)
  end
else
  print 'No columns of type varchar were found'



Show quoteHide quote

Help with this trigger
Sum Total Hours and Minutes in a report
Aggregate Function Error
Joining two tables multiple times
please check