tsql - FIND_IN_SET() equivalent in SQL Server

Question

SELECT * FROM users WHERE uid IN (SELECT doctors FROM MainPage WHERE Valid=1)

users table uid INT
Mainpage table doctors text with value as 1,2,3,4,5

When I am running the above query, it is only resulting 1 row which is for uid = 1, however I wanted all the 5 rows. So in MySQL I used this query:

SELECT *
FROM users
JOIN MainPage ON FIND_IN_SET(uid, doctors)
WHERE Valid = 1;

It worked. I am trying to find an equivalent in SQL Server for FIND_IN_SET to achieve the same result ?

Answer 1

This might work

SELECT *
FROM users u
WHERE EXISTS (
    SELECT *
    FROM MainPage
    WHERE CONCAT(',',doctors,',') LIKE CONCAT('%,',u.uid,',%')
        AND Valid = 1
)