Entire table search 1

[jvande]

What is the SQL to search an entire table for a specific string and return those records that have that string in it. For example I want to search the table "test" for "~". Here is the psuedo code.

select *
from test
where any character = ~

Thanks,
Josh

 

[ccuster]

select * from test where column1 like '%str%'

 

[jvande]

I apoligize. I want a query that searches all 500 of my fields for a "~" and returns the records that have any fields that contain a "~" in it.

 

[rajeevnandanmishra]

Hi Josh,
Try to do like this:
create procedure search
(@table varchar(50), @toSearch varchar(10)) as
declare @strsql varchar(2000)
select @strsql="select * from "+@table+" where 1=2 "
select @strsql=@strsql+" or "+name+
" like '%"+@toSearch+"%' " from syscolumns
where object_name(id)=@table
exec(@strsql)


execute this procedure as :
execute search 'myTable','mySeachExpression'
It will return you all the records depending on your criteria.

Let me know if it works.

 

[rajeevnandanmishra]

Hi josh,
Sorry but just add this condition also after object_name condition.
and type in (47,39)
This will make sure that only char and varchar type columns will be searched.

 

[jvande]

That worked excellent. Thank you very much rajeevnandanmishra

 

[ccuster]

Sorry for misreading Josh.

rajeevnandanmishra, that was a very insightful query, thanks!