Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

FIND_IN_SET() equivalent in SQL Server

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 ?

like image 654
Oggu Avatar asked Jan 12 '17 17:01

Oggu


People also ask

What is Find_in_set?

The FIND_IN_SET() function returns the position of a string within a list of strings.

How does find in set work?

MySQL FIND_IN_SET() returns the position of a string if it is present (as a substring) within a list of strings. The string list itself is a string contains substrings separated by ',' (comma) character.

How do I match Comma Separated Values in MySQL?

Finding value(s) from comma separated MySQL has REGEXP (Regular Express) function. REGEXP (MySQL find values in comma separated string) function can search or filer one or multiple string value in your field data.


1 Answers

This might work

SELECT *
FROM users u
WHERE EXISTS (
    SELECT *
    FROM MainPage
    WHERE CONCAT(',',doctors,',') LIKE CONCAT('%,',u.uid,',%')
        AND Valid = 1
)
like image 103
DVT Avatar answered Oct 31 '22 09:10

DVT