How select an aggregated row in SQL?

I have an table with aggregated columns like this in a SQL Server. The goal is: Show me any row that contain Street A or B.

Declare @street table (col nvarchar (50) )
INSERT INTO @street Values ('Street A'), ('Street B' )

SELECT *
FROM Table 
WHERE Adresses like '%' + @street + '%'

SELECT *
FROM Table 
WHERE Adresses = ( SELECT * FROM  @street )

SELECT *
FROM Table 
WHERE STRING_SPLIT(Adresses,',') in ( SELECT * FROM  @street )

It does not work. I do not get results. Results should be like:

>Solution :

You should get rid of this bad structure and store the data in a better form in future.

Anyway, following your intention to use STRING_SPLIT for that, this would do:

SELECT name, adresses  
FROM yourtable  
WHERE EXISTS 
  (SELECT *  
    FROM STRING_SPLIT(adresses, ',')  
    WHERE value IN ('Street A', 'Street B'));  

You should read the documentation, that’s explained there.

Testing with your sample data succeeds: db<>fiddle