SQL Find column with multiple possible values

I have two tables:

TABLE 1

ID |    NAME   | VALUE
1  |TEST1      | '101;103;102;100'
2  |TEST2      | '101;102'

TABLE 2

|VALUE |
| 100  |
| 102  |

I need the rows from Table 1 where i can have ALL values from table 2. The values from table 2 are dynamic, so i need to create a procedure to search for these possible values.

My output should be in that example:

1  |TEST1      | '101;103;102;100'

Answer

You can do this by doing a join by trying to find the table 2 values in table 1. Then you can make sure all the values are getting joined. In SQL Server, something like this could work:

;with table1 as
(select 1 as ID,'test1' as name,'101;103;102;100' as Value union
select 2 as ID,'test2' as name,'101;102' as Value),
table2 as
(select '100' as value union select '102' as value)

select distinct a.ID,a.name,a.Value,count(*) as cnt
from table1 a
inner join table2 b on CHARINDEX(b.value,a.value,1)>0
Group by a.ID,a.name,a.Value
having count(*)=(select count(*) from table2)

That being said, the way the data is structured is really bad design and you should look into fixing the data model. Hope this helps.