sql search from csv string

Question

im doing a search page where i have to search multiple fields with a single textbox. so i will get the search text as a CSV string in my stored procedure

My table is as below

ID  Name          age   
5   bob           23    
6   bod.harry     34    
7   charles       44    

i need a sql query something like this

declare @searchtext='bob,harry,charley'
select * from employee where  name like (@searchtext) 

this query should return both this records (id 5 and 6)

Answer 1

You can use this way in Stored Procedure,

declare @searchtext varchar(1000)

set searchtext ='bob,harry,charley'

declare @filter varchar(2000)

set @filter = '(name LIKE ''%' + replace('bob,harry,charley',',','%'' OR name LIKE ''%') + '%'')'

exec
('
    select *
    from mytab
    where ' + @filter + '
'
)