SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table yourtable (name varchar(100), adresses varchar(100))
insert into yourtable values ('Mike','Street A, Street B, Street C');
insert into yourtable values ('Smith','Street B');
insert into yourtable values ('Jimmy','Street C');
3 rows affected

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


name
adresses


Mike
Street A, Street B, Street C

Smith
Street B