I have two dynamic lists. I want to verify one list contains the strings of other list.
for Ex:
list1 = 'test1, test2, test3, test4, test5'
list2 = 'how, where, why, test2, test1'
I want to validate the second list, whether every string of list2 is in list1 or not? I want to use it in the stored procedure in SQL server.
Could you please provide the solution?
you may try the below method also
declare @l1 varchar(max),@l2 varchar(max),@result varchar(max)
set @l1 = 'test1, test2, test3, test4, test5'
set @l2 = 'how, where, why, test2, test1'
select 'l1' as id,ltrim(t.c.value('.','varchar(max)')) as n into #t1 from
(select x= cast('<t>'+replace(@l1,',','</t><t>')+'</t>' as xml)) a cross apply x.nodes('/t') t(c)
select 'l2' as id,ltrim(t.c.value('.','varchar(max)')) as n into #t2 from
(select x= cast('<t>'+replace(@l2,',','</t><t>')+'</t>' as xml)) a cross apply x.nodes('/t') t(c)
select @result= (select n+',' from #t1 where n in(select n from #t2) for xml path(''))
drop table #t1, #t2
select @result
columnwith multiple rows or declared variable with list values.