MySQL FIND_IN_SET(array1, array2)

Question

Is it possible to search for an array of numbers in a comma separated column?

I know that this is possible

SELECT * FROM products WHERE FIND_IN_SET('1', '1,3,5') > 0

But is this possible too?

SELECT * FROM products WHERE FIND_IN_SET('1,2', '1,3,5') > 0

If 1 or 2 be in the column it should return > 0

if you know another method just let me know

thank you

Any particular reason to keep a comma separated column instead of normalising the database? — Joachim Isaksson
– Joachim Isaksson, Commented Jan 1, 2014 at 13:52
@JoachimIsaksson what is the meaning of normalising the database? — Mohammad Masoudian
– Mohammad Masoudian, Commented Jan 1, 2014 at 13:53
It means that you don't store comma separated values in the database, but instead make a separate table that has a row per value and references the original table. See for example this question/answers for why it's generally a bad idea to store comma separated lists in the database. — Joachim Isaksson
– Joachim Isaksson, Commented Jan 1, 2014 at 13:56
@JoachimIsaksson imagine a school containing students that each student registered for one or more classes. i have a users table and a class table. each user has a row in database and each class has a row in database too. i have a user_class column in users table that save the classes that registered by each user. if a student registered for 2 classes in the same time i think that i need to save his registered classes in user_class column like this '1,2'. is this possible to normalize this? thank you — Mohammad Masoudian
– Mohammad Masoudian, Commented Jan 1, 2014 at 14:06
The straight forward approach would be to create a user_class table that has a row per student/class with a studentid field and a classid field, that is, if student1 and student2 both registered for class1 and student1 also registered for class2, you'd have 3 rows, student1 - class1, student2 - class2, student1 - class2. That makes it trivial to - for example - count number of registered students in a class, which is hard with a comma separated list in the student table. — Joachim Isaksson
– Joachim Isaksson, Commented Jan 1, 2014 at 14:10

juergen d · Accepted Answer · 2018-01-12 15:38:38Z

2

No, but you can use find_in_set multiple times

SELECT * FROM products
WHERE FIND_IN_SET(1, '1,3,5') > 0
   OR FIND_IN_SET(2, '1,3,5') > 0

edited Jan 12, 2018 at 15:38

answered Jan 1, 2014 at 13:48

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL FIND_IN_SET(array1, array2)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related