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 usersJOIN 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 ?

Since SQL Server 2016 (13.x) there is STRING_SPLIT()

SELECT *FROM usersWHERE uid IN(SELECT value FROM STRING_SPLIT((SELECT doctors FROM MainPage WHERE Valid = 1),','))

Accepted Answer

This might work

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