Below is my table,
create table t( id int, colParam varchar(max)) insert into t values(1,'["param1", "param2"]') insert into t values(2,'["param2"]') insert into t values(3,'["param1"]') insert into t values(4,'["param2", "param3"]') insert into t values(5,'["param1", "param2"]')
tried
declare @str varchar(max) = 'param1'; Select * from t where colParam like '%'+ @str+'%'
its not working for
declare @str varchar(max) = 'param1,param2'; Select * from t where colParam like '%'+ @str+'%'
i want to select rows by passing colPar as 'param1,param2'
so it will result me all the records containing param1 and param2 in colParam
Advertisement
Answer
This quiet tricky.
create table #t( id int, colParam varchar(max) ) insert into #t values(1,'["param1", "param2"]') insert into #t values(2,'["param2"]') insert into #t values(3,'["param1"]') insert into #t values(4,'["param2", "param3"]') insert into #t values(5,'["param1", "param2"]') declare @str varchar(max) = 'param1,param2';
to Return all matching values.
select distinct id, t1.colParam from #t t1 cross apply string_split(t1.colParam, ',') t2 cross apply string_split(@str, ',') t3 where t2.value like '%'+t3.value+'%'
Output: