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 ?

Solution

This might work

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

Comments

[Adir D]

Maybe instead you could change your design so you aren't storing multiple facts as single values - in other words you should have a table with DoctorID and UID columns, both ints. Barring doing it the right way, you need a split function. In SQL Server 2016 you can use STRING_SPLIT but prior to that your options are limited and not very efficient.

[Oggu]

I tried it DVT, query only considered 1 from the 1,2,3,4,5. anything else that we can try ?

[Oggu]

My Bad, it works correct. But looks like it will be a performance hit !!!

[Oggu]

i found another way !!

[Oggu]

declare @x varchar(1000) set @x=(select doctors from mainpage where valis=1) exec 'SELECT * FROM users u WHERE uid in ('+@x+')')

[Lukasz Szozda]

Looks like entire question is XY Problem. stackoverflow.com/a/36980115/5070879

[Vasiliy Zverev]

@LukaszSzozda, I don't agree. This question is not related to parameterized queries though it looks similar at the first glance. Here is a case when you store comma separated list of ids in database.