1

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

Mohammad Masoudian
  • 3,483
  • 7
  • 27
  • 45
  • 1
    Any particular reason to keep a comma separated column instead of normalising the database? – Joachim Isaksson Jan 01 '14 at 13:52
  • @JoachimIsaksson what is the meaning of normalising the database? – Mohammad Masoudian Jan 01 '14 at 13:53
  • 2
    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](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) for why it's generally a bad idea to store comma separated lists in the database. – Joachim Isaksson Jan 01 '14 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 Jan 01 '14 at 14:06
  • 1
    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 Jan 01 '14 at 14:10

1 Answers1

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
juergen d
  • 201,996
  • 37
  • 293
  • 362