How to select from string array added in SQL column?

Question

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

Answer 1

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: