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

So based on your sample data it should return only ID=1 and 5? — Ed Bangga
– Ed Bangga, Commented Dec 12, 2019 at 7:25
No, i should return all, because param1 and param2 exists in all records. When i say param3 it should return Id =4, on param1 it should return Id =1,3,5 — user1638526
– user1638526, Commented Dec 12, 2019 at 8:05

Ed Bangga · Accepted Answer · 2019-12-12 08:08:45Z

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:

edited Dec 12, 2019 at 8:08

answered Dec 12, 2019 at 7:31

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1638526 Over a year ago

I tried with the cross apply before but used only once in query. Your efforts solved my query and time. Thank you.

StepUp · Accepted Answer · 2019-12-12 07:17:10Z

1

If you are using SQL Server 2016 and above, then you can use STRING_SPLIT.

As MSDN says:

A table-valued function that splits a string into rows of substrings, based on a specified separator character.

So your can look like this:

DECLARE @t TABLE
(
    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"]')

SELECT 
  t.id
, s.col
FROM @t AS t
OUTER APPLY
(
    SELECT 
    spl.value AS col
    FROM STRING_SPLIT(
        (SELECT _t.colParam FROM @t AS _t WHERE _t.id = t.id), ',') AS spl
 )s

answered Dec 12, 2019 at 7:17

StepUp

38.5k16 gold badges95 silver badges159 bronze badges

Collectives™ on Stack Overflow

How to select from string array added in SQL column?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related