How can I select using an intersection of comma-separated value?

Question

I have a column holds a comma-separated values.

1,2,3
4,6,7
2,3,8
12234,5467,232445,232455,11223

With given criteria of array (e.g., 1,4,9),

How can I select rows whose value contains any of given?

I mean when I am given with 1,4,9, I need to select

1,2,3 -- has 1
4,6,7 -- has 4

UPDATE

I have a table who has a column of comma-separated values of other entity's primary keys. I understand the reason why the original table designer did this. The other entity actually resides in other database which means not joinable. Or he or she just wanted to do like this.

The STUDENT table

id     name     classes
---------------------------
1      John     1,2,3
2      Jane     2,8,233423423

The Criteria

With given comma-separated class numbers, find students who is attending any of them.

given: 1           -> select John
given: 233423423   -> select Jane
given: 1,233423423 -> select Both

Does you column hold only single digit numbers or they can be 2 or more digits as well? — Poorvi Nigotiya, May 04 '17 at 05:24
Please provide more context, e.g. the `createt table ...` lines for involved tables. Which kind of array are you using? — Yunnosch, May 04 '17 at 05:25
Are you satisfied with the answer below? It uses one hardcoded query line for each number in the array. Or do you have some kind of technical array? Maybe the numbers are in a separate table? — Yunnosch, May 04 '17 at 05:35
Why do you think you can't do joins across databases? Read https://dev.mysql.com/doc/refman/5.7/en/identifier-qualifiers.html — Bill Karwin, May 04 '17 at 06:38

Alexander · Answer 1 · 2017-05-04T11:52:18.503

2

You can use dynamic template for regular expression. For example:

SET @Criteria='1,4,9';
SELECT `name` 
FROM STUDENT 
WHERE STUDENT.classes REGEXP concat('(^|,)(', REPLACE(@Criteria, ',', '|'), ')(,|$)');

edited May 04 '17 at 11:52

answered May 04 '17 at 05:54

Alexander

4,420
7
27
42

1

The basic idea is O.K. but you need to avoid false-positives for partial matches: `REGEXP concat('(^|,)(',REPLACE(@MySet, ',', '|'),')(,|$)')` – David דודו Markovitz May 04 '17 at 06:07

score 1 · Accepted Answer · edited May 23 '17 at 11:47

If you have an input 1,4,9 and you have to find rows where any of 1, 4, or 9 occur in a comma-separated list?

SELECT ...
FROM MyTable
WHERE FIND_IN_SET(1, mycolumn) 
OR FIND_IN_SET(4, mycolumn) 
OR FIND_IN_SET(9, mycolumn)

See https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set for information about this function.

This should illustrate to you that storing comma-separated lists is not a good idea for a relational database, when you want to treat the elements of the list as discrete values.

See also my answer to Is storing a delimited list in a database column really that bad?

The additional information by OP is younger than your answer, but now you seem off target. — Yunnosch, May 04 '17 at 06:19

How can I select using an intersection of comma-separated value?

The STUDENT table

The Criteria

2 Answers2