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 ?

In MySQL I assume you have a SET typed column. The closest match to this would be an INT or BIGINT column, used as a flags bitmap. — Ben, Jan 12 '17 at 17:21
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`](https://sqlperformance.com/2016/03/sql-server-2016/string-split) but prior to that [your options are limited and not very efficient](https://sqlperformance.com/2012/07/t-sql-queries/split-strings). — Aaron Bertrand, Jan 12 '17 at 17:46

DVT · Accepted Answer · 2017-01-12T17:55:26.560

6

This might work

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

edited Jan 12 '17 at 17:55

answered Jan 12 '17 at 17:27

DVT

3,014
1
13
19

I tried it DVT, query only considered 1 from the 1,2,3,4,5. anything else that we can try ? – Oggu Jan 23 '17 at 16:56
My Bad, it works correct. But looks like it will be a performance hit !!! – Oggu Jan 23 '17 at 20:44
i found another way !! – Oggu Jan 30 '17 at 23:03
declare @x varchar(1000) set @x=(select doctors from mainpage where valis=1) exec 'SELECT * FROM users u WHERE uid in ('+@x+')') – Oggu Jan 30 '17 at 23:03

score 2 · Answer 2 · answered Dec 13 '19 at 13:55

2

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

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

answered Dec 13 '19 at 13:55

Vasiliy Zverev

622
5
10

Looks like entire question is XY Problem. https://stackoverflow.com/a/36980115/5070879 – Lukasz Szozda Dec 13 '19 at 14:00
@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. – Vasiliy Zverev Dec 13 '19 at 15:27

FIND_IN_SET() equivalent in SQL Server

2 Answers2

Linked

Related