By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Category ( id int , related_brand_id varchar(100));
insert into Category values (1,'brand1,brand3,brand5'),(2,'brand3,brand5'),(3,'brand5'),(4,'brand2,brand5');
4 rows affected
Declare @brand_id varchar(50) ='brand1,brand2';
select C.*
from Category c
cross apply (SELECT VALUE FROM string_split(related_brand_id,',')) s
join (SELECT VALUE FROM string_split('brand1,brand2',',')) v
on s.value = v.value
| id | related_brand_id |
|---|---|
| 1 | brand1,brand3,brand5 |
| 4 | brand2,brand5 |