Problem with IN expression

SELECT * from tablename
WHERE 5 IN (Ids)

Ids is a varchar containing either a single id, or a comma separated list of several ids.

The above query works fine if Ids contains a single id (5 for example). But if Ids is a list (1,2,3,4,5), then the above query returns an empty result set, which is wrong.

However the following query works on the same data:

SELECT * from tablename
WHERE 1 IN (Ids)

So why does the query find 1 in the list (1,2,3,4,5), but not 5?
JohnStevensonAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
todd_farmerConnect With a Mentor Commented:
Typo correction:

SELECT * from tablename
WHERE 5 FIND_IN_SET (Ids)
0
 
todd_farmerCommented:
Use FIND_IN_SET:

SELECT * from tablename
WHERE 5 FIN_IN_SET (Ids)
0
 
JohnStevensonAuthor Commented:
Thanks Todd

But I get an error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FIND_IN_SET(Ids)

And I cannot find any MySql documentation about FIND_IN_SET.
0
 
JohnStevensonAuthor Commented:
Ok, sorted it.

SELECT * from tablename
WHERE FIND_IN_SET (5, Ids)

Regarding FIND_IN_SET documentation. The MySql website search gives no results, but Google finds lots of relevant pages from the MySql website!

Thanks for your help

John
0
 
todd_farmerCommented:
Sorry about that - thanks for the points!
0
All Courses

From novice to tech pro — start learning today.