WHERE string IN string SQL Server

Question

Right now I'm in a trouble and donot know how to solve it.

Declare @brand_id varchar(50) ='brand1, brand2'

SELECT related_brand_id FROM dbo.Category

=> result: Row 1: "brand1, brand3, brand5"
           Row 2: "brand3, brand5"
           Row 3: "brand5"
           Row 4: "brand2, brand5"
           ...

So, how can I select all rows in table Category with param @brand_id contain IN related_brand_id?

My expectation result:

Row 1: "brand1, brand3, brand5"
Row 4: "brand2"
...

Answer 1

In SQL Server 2016 and above, you can use string_split:

DECLARE @brand_id varchar(50) = 'brand1,brand2';

SELECT distinct C.*
FROM Category c
CROSS APPLY
    (SELECT VALUE FROM string_split(related_brand_id, ',')) s
JOIN
    (SELECT VALUE FROM string_split(@brand_id , ',')) v ON TRIM(s.value) = TRIM(v.value)

Output:

id | related_brand_id    
---+-------------------
 1 | brand1,brand3,brand5
 4 | brand2,brand5       

db<>fiddle here

Answer 2

If replace your search string with these two similar strings:

Declare @brand_id1 varchar(50) = '%brand1%'
Declare @brand_id2 varchar(50) = '%brand2%'

Then you can select all rows that contain either of those strings with the following code:

SELECT related_brand_id FROM dbo.Category
WHERE related_brand_id like @brand_id1 or related_brand_id like @brand_id2

This would return your expected result.